package schoolsource.sql;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.Hashtable;
import java.util.logging.Level;

import schoolsource.config.ConfigurationItems;
import schoolsource.model.AnnouncementContainer;
import schoolsource.model.AttachmentContainer;
import schoolsource.model.AttendanceContainer;
import schoolsource.model.BooleanItem;
import schoolsource.model.ClassContainer;
import schoolsource.model.ContactContainer;
import schoolsource.model.CourseContainer;
import schoolsource.model.DateItem;
import schoolsource.model.DateNoTimeItem;
import schoolsource.model.GradeContainer;
import schoolsource.model.HomeworkContainer;
import schoolsource.model.HourContainer;
import schoolsource.model.IntegerItem;
import schoolsource.model.Item;
import schoolsource.model.MessageContainer;
import schoolsource.model.PairedUserContainer;
import schoolsource.model.PasswordItem;
import schoolsource.model.PeriodContainer;
import schoolsource.model.StringItem;
import schoolsource.model.SubmissionContainer;
import schoolsource.model.TermContainer;
import schoolsource.model.UserContainer;
import schoolsource.util.SchoolLogger;
import schoolsource.util.Types;

/**
 *
 * @author Steven Steele
 * Copyright 2003
 *
 * Contains all of the specialized database queries.
 */
public class SchoolDBSQL {

    // SchoolDBSQL HashTable
    private static final Hashtable dbHashTable = new Hashtable();

    // School table columns
    public static final String SCHOOLTABLE = "SCHOOL";
    public static final String SCHOOLID = "SCHOOL_ID";
    public static final String SCHOOLNAME = "SCHOOL_NAME";
    public static final String SCHOOLADDR1 = "SCHOOL_ADDR1";
    public static final String SCHOOLADDR2 = "SCHOOL_ADDR2";
    public static final String SCHOOLCITY = "SCHOOL_CITY";
    public static final String SCHOOLSTATE = "SCHOOL_STATE";
    public static final String SCHOOLZIP = "SCHOOL_ZIP";
    public static final String SCHOOLLOGO = "SCHOOL_LOGO";
    public static final String SCHOOLLOGOWIDTH = "SCHOOL_LOGO_WIDTH";
    public static final String SCHOOLACTIVEMENUBARCOLOR = "SCHOOL_ACTIVE_MENU_BAR_COLOR";
    public static final String SCHOOLINACTIVEMENUBARCOLOR = "SCHOOL_INACTIVE_MENU_BAR_COLOR";
    public static final String SCHOOLACTIVELINKCOLOR = "SCHOOL_ACTIVE_LINK_COLOR";
    public static final String SCHOOLINACTIVELINKCOLOR = "SCHOOL_INACTIVE_LINK_COLOR";
    public static final String SCHOOLMENUHEADINGCOLOR = "SCHOOL_MENU_HEADING_COLOR";
    public static final String SCHOOLMENUBACKGROUNDCOLOR = "SCHOOL_MENU_BACKGROUND_COLOR";
    public static final String SCHOOLHEADINGFONT = "SCHOOL_HEADING_FONT";
    
    public static final String QTR1START = "QTR_1_START";
    public static final String QTR1END = "QTR_1_END";
    public static final String QTR2START = "QTR_2_START";
    public static final String QTR2END = "QTR_2_END";
    public static final String QTR3START = "QTR_3_START";
    public static final String QTR3END = "QTR_3_END";
    public static final String QTR4START = "QTR_4_START";
    public static final String QTR4END = "QTR_4_END";
    
    // term table columns
    public static final String TERMTABLE = "TERM";
    public static final String TERMASSIGNEDID = "TERM_ASSIGNED_ID";
    public static final String TERMNAME = "TERM_NAME";
    public static final String TERMSTART = "TERM_START_DAY";
    public static final String TERMEND = "TERM_END_DAY";

    // term table columns
    public static final String DELTERMTABLE = "DELTERM";
    public static final String DELTERMDELETEDATE = "DELTERM_DELETED_DATE";

    // user table columns
    public static final String USERTABLE = "USER";
    public static final String USRASSIGNEDID = "USR_ASSIGNED_ID";
    public static final String USRSCHOOLID = "USR_SCHOOL_ID";
    public static final String USRPASSWORD = "USR_PASSWORD";
    public static final String USRPSWDCHGREQ = "USR_PSWD_CHG_REQ";
    public static final String USRFNAME = "USR_FNAME";
    public static final String USRLNAME = "USR_LNAME";
    public static final String USRTITLE = "USR_TITLE";
    public static final String USREMAIL = "USR_EMAIL";
    public static final String USRPHONE = "USR_PHONE";
    public static final String USRFAILURES = "USR_FAILURES";
    public static final String USRLASTLOGIN = "USR_LAST_LOGIN";
    public static final String USRTYPE = "USR_TYPE";
    public static final String USRPICTURENAME = "USR_PICTURE_NAME";

    // valid user types columns
    public static final String PARENT = "P";
    public static final String STUDENT = "S";
    public static final String TEACHER = "T";
    public static final String ADMIN = "A";

    // deluser table columns
    public static final String DELUSERTABLE = "DELUSER";
    public static final String DELUSRDELETEDDATE = "DELUSR_DELETED_DATE";
    // and the rest of USER

    // relatesto table columns
    public static final String RELATESTOTABLE = "RELATESTO";
    public static final String PARENTUSRASSIGNEDID = "PARENT_USR_ASSIGNED_ID";
    public static final String STUDENTUSRASSIGNEDID = "STUDENT_USR_ASSIGNED_ID";

    // delrelatesto table columns
    public static final String DELRELATESTOTABLE = "DELRELATESTO";
    public static final String DELRELATESTODELETEDDATE =
        "DELRELATESTO_DELETED_DATE";
    // and the rest of RELATESTO

    // course table columns
    public static final String COURSETABLE = "COURSE";
    public static final String COURSEASSIGNEDID = "COURSE_ASSIGNED_ID";
    public static final String COURSESHORTNAME = "COURSE_SHORT_NAME";
    public static final String COURSEDESCRIPTION = "COURSE_DESCRIPTION";
    public static final String COURSESCHOOLID = "COURSE_SCHOOL_ID";
    //public static final String SCHOOLASSIGNEDID = "SCHOOL_ASSIGNED_ID";

    // delcourse table columns
    public static final String DELCOURSETABLE = "DELCOURSE";
    public static final String DELCOURSEDELETEDDATE = "DELCOURSE_DELETED_DATE";
    // and the rest of course

    // class table columns
    public static final String CLASSTABLE = "CLASS";
    public static final String CLASSASSIGNEDID = "CLASS_ASSIGNED_ID";
    // public static final String COURSEASSIGNEDID = "COURSE_ASSIGNED_ID";
    public static final String CLASSSCHOOLID = "CLASS_SCHOOL_ID";
    public static final String CLASSTIME = "CLASS_TIME";

    // period table columns
    public static final String PERIODTABLE = "PERIOD";
    public static final String PERIODNAME = "PERIOD_NAME";
    public static final String PERIODDAYOFWEEK = "PERIOD_DAY_OF_WEEK";
    public static final String PERIODSTARTTIME = "PERIOD_START_TIME";
    public static final String PERIODENDTIME = "PERIOD_END_TIME";

    // delclass table columns
    public static final String DELCLASSTABLE = "DELCLASS";
    public static final String DELCLASSDELETEDDATE = "DELCLASS_DELETED_DATE";
    // and the rest of class

    // grade table columns
    public static final String GRADETABLE = "GRADE";
    // public static final String USR_ASSIGNED_ID = "USR_ASSIGNED_ID";
    // public static final String CLASS_ASSIGNED_ID = "CLASS_ASSIGNED_ID";
    // public static final String HW_ASSIGNED_ID = "HW_ASSIGNED_ID";
    public static final String GRADESCORE = "GRADE_SCORE";
    public static final String GRADECOMMENT = "GRADE_COMMENT";

    // delgrade table columns
    public static final String DELGRADETABLE = "DELGRADE";
    public static final String DELGRADEDELETEDDATE = "DELGRADE_DELETED_DATE";
    // and the rest of grade

    // homework table columns
    public static final String HOMEWORKTABLE = "HOMEWORK";
    public static final String HWASSIGNEDID = "HW_ASSIGNED_ID";
    // public static final String CLASS_ASSIGNED_ID = "CLASS_ASSIGNED_ID";
    public static final String HWDESCRIPTION = "HW_DESCRIPTION";
    public static final String HWTOTALPOINTS = "HW_TOTAL_POINTS";
    public static final String HWDATEASSIGNED = "HW_DATE_ASSIGNED";
    public static final String HWDATEDUE = "HW_DATE_DUE";
    public static final String HWSHORTNAME = "HW_SHORT_NAME";
    public static final String HWANSWERS = "HW_ANSWERS";
    public static final String HWNOTES = "HW_NOTES";
    public static final String HWDATEUPDATED = "HW_DATE_UPDATED";
    public static final String HWSUBMISSIONSALLOWED = "HW_SUBMISSIONS_ALLOWED";

    // deleted homework table columns
    public static final String DELHOMEWORKTABLE = "DELHOMEWORK";
    public static final String DELHWDELETEDDATE = "DELHW_DELETED_DATE";
    // and the rest of homework

    // message table columns
    public static final String MESSAGETABLE = "MESSAGE";
    public static final String MSGASSIGNEDID = "MSG_ASSIGNED_ID";
    public static final String MSGTIMESENT = "MSG_TIME_SENT";
    public static final String MSGSUBJECT = "MSG_SUBJECT";
    public static final String MSGBODY = "MSG_BODY";

    // deleted message table columns
    public static final String DELMESSAGETABLE = "DELMESSAGE";
    public static final String DELMSGDELETEDDATE = "DELMSG_DELETED_DATE";
    // and the rest of message

    // announces table columns
    public static final String ANNOUNCESTABLE = "ANNOUNCES";
    // public static final String MSGASSIGNEDID = "MSG_ASSIGNED_ID";
    // public static final String USRASSIGNEDID = "USR_ASSIGNED_ID";
    // public static final String CLASSASSIGNEDID = "CLASS_ASSIGNED_ID";
    public static final String ANNHIDDEN = "ANN_HIDDEN";

    // deleted announces table columns
    public static final String DELANNOUNCESTABLE = "DELANNOUNCES";
    public static final String DELANNDELETEDDATE = "DELANN_DELETED_DATE";
    // and the rest of announces

    // attends table columns
    public static final String ATTENDSTABLE = "ATTENDS";
    // public static final String USRASSIGNEDID = "USR_ASSIGNED_ID";
    // public static final String CLASSASSIGNEDID = "CLASS_ASSIGNED_ID";

    // deleted attends table columns
    public static final String DELATTENDSTABLE = "DELATTENDS";
    public static final String DELATTENDSDELETEDDATE =
        "DELATTENDS_DELETED_DATE";
    // and the rest of attends

    // contacts table columns
    public static final String CONTACTSTABLE = "CONTACTS";
    // public static final String MSGASSIGNEDID = "MSG_ASSIGNED_ID";
    public static final String CTTOID = "CT_TO_ID";
    public static final String CTFROMID = "CT_FROM_ID";
    // public static final String CLASSASSIGNEDID = "CLASS_ASSIGNED_ID"
    public static final String CTHIDDEN = "CT_HIDDEN";

    // deleted contacts table columns
    public static final String DELCONTACTSTABLE = "DELCONTACTS";
    public static final String DELCTDELETEDDATE = "DELCT_DELETED_DATE";
    // and the rest of contacts

    // teaches table columns
    public static final String TEACHESTABLE = "TEACHES";
    // public static final String USRASSIGNEDID = "USR_ASSIGNED_ID";
    // public static final String CLASSASSIGNEDID = "CLASS_ASSIGNED_ID";

    // deleted teaches table columns
    public static final String DELTEACHESTABLE = "DELTEACHES";
    public static final String DELTEACHESDELETEDDATE =
        "DELTEACHES_DELETED_DATE";
    // and the rest of teaches

    // attachment table columns
    public static final String ATTACHMENTTABLE = "ATTACHMENT";
    public static final String ATTASSIGNEDID = "ATT_ASSIGNED_ID";
    //  public static final String ATTFILESTOREDNAME = "ATT_FILE_STORED_NAME";
    //  public static final String ATTFILEORIGNAME = "ATT_FILE_ORIG_NAME";
    public static final String ATTFILEPATH = "ATT_FILE_PATH";
    public static final String ATTFILENAME = "ATT_FILE_NAME";
    public static final String ATTFILESIZE = "ATT_FILE_SIZE";

    // deleted attachment table columns
    public static final String DELATTACHMENTTABLE = "DELATTACHMENT";
    public static final String DELATTDELETEDDATE = "DELATT_DELETED_DATE";
    // and the rest of attachment

    // msgincludes table columns
    public static final String MSGINCLUDESTABLE = "MSGINCLUDES";
    // public static final String MSGASSIGNEDID = "MSG_ASSIGNED_ID";

    // deleted msgincludes table columns
    public static final String DELMSGINCLUDESTABLE = "DELMSGINCLUDESTABLE";
    public static final String DELMSGINCDELETEDDATE = "DELMSGINC_DELETED_DATE";
    // and the rest of msgincludes

    // hwincludes table columns
    public static final String HWINCLUDESTABLE = "HWINCLUDES";
    // public static final String ATTACHMENTTABLE = "ATTACHMENT";
    // public static final String HWASSIGNEDID = "HW_ASSIGNED_ID";

    // deleted hwincludes table columns
    public static final String DELHWINCLUDESTABLE = "DELHWINCLUDES";
    public static final String DELHWINCDELETEDDATE = "DELHWINC_DELETED_DATE";
    // and the rest of hwincludes

    // attendance table columns
    public static final String ATTENDANCETABLE = "ATTENDANCE";
    // public static final String USRASSIGNEDID = "USR_ASSIGNED_ID";
    // public static final String CLASSASSIGNEDID = "CLASS_ASSIGNED_ID";
    public static final String ATNDDATE = "ATND_DATE";
    public static final String ATNDTYPE = "ATND_TYPE";

    // submission table columns
    public static final String SUBMISSIONTABLE = "SUBMISSION";
//    public static final String CLASSASSIGNEDID = "CLASS_ASSIGNED_ID";
//    public static final String HWASSIGNEDID = "HW_ASSIGNED_ID";
//    public static final String USRASSIGNEDID = "USR_ASSIGNED_ID";
    public static final String SUBMISSIONFILENAME = "SUBMISSION_FILE_NAME";
    public static final String SUBMISSIONFILEPATH = "SUBMISSION_FILE_PATH";
    public static final String SUBMISSIONFILESIZE = "SUBMISSION_FILE_SIZE";
    public static final String SUBMISSIONUPDATEALLOWED = "SUBMISSION_UPDATE_ALLOWED";
    public static final String SUBMISSIONDATE = "SUBMISSION_DATE";

    private String dataSource = null;
    private String unprefixedDataSource = null;

    // Instances of table accessors
    private final MySQLAccessor userTable;
    private final MySQLAccessor delUserTable;
    private final MySQLAccessor relatesToTable;
    private final MySQLAccessor delRelatesToTable;
    private final MySQLAccessor messageTable;
    private final MySQLAccessor delMessageTable;
    private final MySQLAccessor courseTable;
    private final MySQLAccessor delCourseTable;
    private final MySQLAccessor classTable;
    private final MySQLAccessor periodTable;
    private final MySQLAccessor delClassTable;
    private final MySQLAccessor homeworkTable;
    private final MySQLAccessor delHomeworkTable;
    private final MySQLAccessor gradeTable;
    private final MySQLAccessor delGradeTable;
    private final MySQLAccessor teachesTable;
    private final MySQLAccessor delTeachesTable;
    private final MySQLAccessor attendsTable;
    private final MySQLAccessor delAttendsTable;
    private final MySQLAccessor announcesTable;
    private final MySQLAccessor delAnnouncesTable;
    private final MySQLAccessor schoolTable;
    private final MySQLAccessor termTable;
    private final MySQLAccessor delTermTable;
    private final MySQLAccessor contactsTable;
    private final MySQLAccessor delContactsTable;
    private final MySQLAccessor attachmentTable;
    private final MySQLAccessor delAttachmentTable;
    private final MySQLAccessor msgIncludesTable;
    private final MySQLAccessor delMsgIncludesTable;
    private final MySQLAccessor hwIncludesTable;
    private final MySQLAccessor delHWIncludesTable;
    private final MySQLAccessor attendanceTable;
    private final MySQLAccessor submissionTable;



    /**
     * Factory method for SchoolDBSQL. Returns a new instance only if one
     * has not been created before.
     * @param dataSource The name of the data source of interest.
     * @return An instance of SchoolDBSQL that accesses the data source of interest.
     */
    public static SchoolDBSQL getSchoolDBSQL(String dataSource) {
        SchoolDBSQL returnVal = (SchoolDBSQL) dbHashTable.get(dataSource);

        if (returnVal == null) {
// made this change
//            returnVal = new SchoolDBSQL("jdbc:mysql://localhost/mystud2_" + dataSource);
//            returnVal = new SchoolDBSQL("jdbc:caucho:" + dataSource);
            returnVal = new SchoolDBSQL(ConfigurationItems.DATASOURCEPREFIX + dataSource);
            returnVal.unprefixedDataSource = dataSource;
            dbHashTable.put(dataSource, returnVal);
        }

        return returnVal;
    }

    /**
     * Returns a new instance with all of the accessor objects created for the data
     * source of interest.
     * @param newDataSource The name of the data source of interest.
     */
    private SchoolDBSQL(String newDataSource) {
        dataSource = newDataSource;
        userTable = new MySQLAccessor(dataSource, USERTABLE);
        delUserTable = new MySQLAccessor(dataSource, DELUSERTABLE);
        relatesToTable = new MySQLAccessor(dataSource, RELATESTOTABLE);
        delRelatesToTable = new MySQLAccessor(dataSource, DELRELATESTOTABLE);
        messageTable = new MySQLAccessor(dataSource, MESSAGETABLE);
        delMessageTable = new MySQLAccessor(dataSource, DELMESSAGETABLE);
        courseTable = new MySQLAccessor(dataSource, COURSETABLE);
        delCourseTable = new MySQLAccessor(dataSource, DELCOURSETABLE);
        classTable = new MySQLAccessor(dataSource, CLASSTABLE);
        delClassTable = new MySQLAccessor(dataSource, DELCLASSTABLE);
        periodTable = new MySQLAccessor(dataSource, PERIODTABLE);
        homeworkTable = new MySQLAccessor(dataSource, HOMEWORKTABLE);
        delHomeworkTable = new MySQLAccessor(dataSource, DELHOMEWORKTABLE);
        gradeTable = new MySQLAccessor(dataSource, GRADETABLE);
        delGradeTable = new MySQLAccessor(dataSource, DELGRADETABLE);
        teachesTable = new MySQLAccessor(dataSource, TEACHESTABLE);
        delTeachesTable = new MySQLAccessor(dataSource, DELTEACHESTABLE);
        attendsTable = new MySQLAccessor(dataSource, ATTENDSTABLE);
        delAttendsTable = new MySQLAccessor(dataSource, DELATTENDSTABLE);
        announcesTable = new MySQLAccessor(dataSource, ANNOUNCESTABLE);
        delAnnouncesTable = new MySQLAccessor(dataSource, DELANNOUNCESTABLE);
        schoolTable = new MySQLAccessor(dataSource, SCHOOLTABLE);
        termTable = new MySQLAccessor(dataSource, TERMTABLE);
        delTermTable = new MySQLAccessor(dataSource, DELTERMTABLE);
        contactsTable = new MySQLAccessor(dataSource, CONTACTSTABLE);
        delContactsTable = new MySQLAccessor(dataSource, DELCONTACTSTABLE);
        attachmentTable = new MySQLAccessor(dataSource, ATTACHMENTTABLE);
        delAttachmentTable = new MySQLAccessor(dataSource, DELATTACHMENTTABLE);
        msgIncludesTable = new MySQLAccessor(dataSource, MSGINCLUDESTABLE);
        delMsgIncludesTable = new MySQLAccessor(dataSource, DELMSGINCLUDESTABLE);
        hwIncludesTable = new MySQLAccessor(dataSource, HWINCLUDESTABLE);
        delHWIncludesTable = new MySQLAccessor(dataSource, DELHWINCLUDESTABLE);
        attendanceTable = new MySQLAccessor(dataSource, ATTENDANCETABLE);
        submissionTable = new MySQLAccessor(dataSource, SUBMISSIONTABLE);
    }


    /**
        IntegerItem failedlogins = new IntegerItem(SchoolDBSQL.USRFAILURES, new Integer(-1), true);
     * @param username The school-issued user ID of the user.
     * @param password The password of the user.
     * @param type The type of user.
     * @return The number of consecutive login failures since the last successful login.
     */
    public IntegerItem verifyUser(Item username, Item password, Item type) {
        ArrayList results = null;
        IntegerItem failedlogins = new IntegerItem(SchoolDBSQL.USRFAILURES, new Integer(-1), true);
        String queryString =
            "SELECT " + failedlogins.getFieldName()
                + " FROM " + USERTABLE
                + " WHERE " + username.getFieldName() + " = " + username.getDBFormattedString(userTable)
                + " AND " + type.getFieldName() + " = " + type.getDBFormattedString(userTable)
                + " AND " + password.getFieldName() + " = " + password.getDBFormattedString(userTable)
                + ";";
        results = userTable.performDatabaseQuery(queryString, new Item[] {failedlogins});
        if (results != null && results.size() > 0) {
            failedlogins.setValue(((Hashtable) results.get(0)).get(failedlogins.getFieldName()).toString());            // last login time and failure counts need to be reset, but can't do it
            // here because the student is loaded after this.
        } else {
            queryString =
                "SELECT * FROM " + USERTABLE
                    + " WHERE " + username.getFieldName() + " = " + username.getDBFormattedString(userTable)
                    + ";";
            results = userTable.performDatabaseQuery(queryString, new Item[] {failedlogins});
            if (results != null && results.size() > 0) {                failedlogins.setValue(((Hashtable) results.get(0)).get(failedlogins.getFieldName()).toString());
                String failedLoginUpdate =
                    "UPDATE " + USERTABLE
                        + " SET " + failedlogins.getFieldName()
                        + " = " + (((Integer) failedlogins.getObject()).intValue() + 1)
                        + " WHERE " + username.getFieldName() + " = " + username.getDBFormattedString(userTable)
                        + " AND " + type.getFieldName().toString() + " = " + type.getDBFormattedString(userTable)
                        + ";";
                userTable.performDatabaseUpdate(failedLoginUpdate);             failedlogins.setValue("-1");
            } else {
                // not a valid username so there is nothing to update
            }
        }
        SchoolLogger.logger.log(Level.INFO, "failedlogins != null: " + (failedlogins != null));
        return failedlogins;
    }

    /**
     * Validates that a user can log in given the parameters. This method is used so that
     * the query is not logged as standard queries are.
     * @param assignedID The auto-generated user ID of the user.
     * @param password The password of the user.
     * login failures since the last successful login. This value is populated by the method.
     * @return The number of consecutive login failures since the last successful login.
     */
    public IntegerItem verifyUser(Item assignedID, Item password) {
        ArrayList results = null;
        IntegerItem failedlogins = new IntegerItem(SchoolDBSQL.USRFAILURES, new Integer(-1), true);
        String queryString =
            "SELECT " + failedlogins.getFieldName()
                + " FROM " + USERTABLE + " WHERE " + assignedID.getFieldName() + " = " + assignedID.getDBFormattedString(userTable)
                + " AND " + password.getFieldName() + " = " + password.getDBFormattedString(userTable)
                + ";";
        results = userTable.performDatabaseQuery(queryString, new Item[] {failedlogins});
        if (results != null && results.size() > 0) {
            failedlogins.setValue(((Hashtable) results.get(0)).get(failedlogins.getFieldName()).toString());
            // last login time and failure counts need to be reset, but can't do it
            // here because the student is loaded after this.
        } else {
            queryString =
                "SELECT *"
                    + " FROM " + USERTABLE
                    + " WHERE " + assignedID.getFieldName() + " = " + assignedID.getDBFormattedString(userTable)
                    + ";";
            results = userTable.performDatabaseQuery(queryString, new Item[] {failedlogins});
            if (results != null && results.size() > 0) {
                failedlogins.setValue(((Hashtable) results.get(0)).get(failedlogins.getFieldName()).toString());                String failedLoginUpdate =
                    "UPDATE " + USERTABLE
                        + " SET " + failedlogins.getFieldName() + " = " + (((Integer) failedlogins.getObject()).intValue() + 1)
                        + " WHERE " + assignedID.getFieldName() + " = " + assignedID.getDBFormattedString(userTable)
                        + ";";
                userTable.performDatabaseUpdate(failedLoginUpdate);             failedlogins.setValue("-1");
            } else {
                // not a valid username so there is nothing to update
            }
        }

        SchoolLogger.logger.log(Level.INFO, "failedlogins != null: " + (failedlogins != null));
        return failedlogins;
    }

    /**
     * Validates whether a parent login is valid. Parents may only log in if the provide their
     * school-assigned user ID and their child's school-assigned user ID.
     * @param parentUsername The school-assigned user ID of the parent.
     * @param studentUsername The school-assigned user ID of the parent's child.
     * @param password The password of the parent.
     * @return The number of times the user has failed to log in.
     */
    public IntegerItem verifyParent(Item parentUsername, Item studentUsername, Item password) {
        IntegerItem failedlogins = new IntegerItem(SchoolDBSQL.USRFAILURES, new Integer(-1), true);
        ArrayList results = null;
        String queryString =
            "SELECT U1." + failedlogins.getFieldName()
                + " FROM " + USERTABLE + " AS U1, " + USERTABLE + " AS U2, " + RELATESTOTABLE
                + " WHERE U1." + parentUsername.getFieldName() + " = " + parentUsername.getDBFormattedString(userTable)
                + " AND U1." + USRTYPE + " = '" + PARENT + "'"
                + " AND U1." + password.getFieldName() + " = " + password.getDBFormattedString(userTable)
                + " AND U1." + USRASSIGNEDID + " = " + RELATESTOTABLE + "." + PARENTUSRASSIGNEDID
                + " AND " + RELATESTOTABLE + "." + STUDENTUSRASSIGNEDID + " = U2." + USRASSIGNEDID
                + " AND U2." + studentUsername.getFieldName() + " = " + studentUsername.getDBFormattedString(userTable)
                + ";";
        results = userTable.performDatabaseQuery(queryString, new Item[] {failedlogins});
        if (results != null && results.size() > 0) {
            failedlogins.setValue(((Hashtable) results.get(0)).get(failedlogins.getFieldName()).toString());
            // last login time and failure counts need to be reset, but can't do it
            // here because the student is loaded after this.
        } else {
            queryString =
                "SELECT * FROM " + USERTABLE
                    + " WHERE " + parentUsername.getFieldName() + " = " + parentUsername.getDBFormattedString(userTable)
                    + " AND " + USRTYPE + " = '" + PARENT
                    + "';";
            results = userTable.performDatabaseQuery(queryString, new Item[] {failedlogins});
            if (results != null && results.size() > 0) {
                failedlogins.setValue(((Hashtable) results.get(0)).get(failedlogins.getFieldName()).toString());                String failedLoginUpdate =
                    "UPDATE " + USERTABLE
                        + " SET " + failedlogins.getFieldName() + " = " + (((Integer) failedlogins.getObject()).intValue() + 1)
                        + " WHERE " + parentUsername.getFieldName() + " = " + parentUsername.getDBFormattedString(userTable)
                        + " AND " + USRTYPE + " = '" + PARENT
                        + "';";
                userTable.performDatabaseUpdate(failedLoginUpdate);             failedlogins.setValue("-1");
            } else {
                // not a valid username so there is nothing to update
            }
        }
        SchoolLogger.logger.log(Level.INFO, "failedlogins != null: " + (failedlogins != null));
        return failedlogins;
    }

    /**
     * Sets the number of failures for the user to zero and the date of last login to the current time.
     * @param assignedUserID The auto-generated user ID whose values are to be reset.
     */
    public void resetUser(Item assignedUserID) {
        DateItem di = new DateItem(USRLASTLOGIN, new GregorianCalendar(), false);
        String updateString =
            "UPDATE " + USERTABLE
                + " SET " + USRFAILURES + " = 0, " + di.getFieldName() + " = " + di.getDBFormattedString(userTable)
                + " WHERE " + assignedUserID.getFieldName() + " = " + assignedUserID.getDBFormattedString(userTable)
                + ";";
        userTable.performDatabaseUpdate(updateString);
    }

    /**
     * Loads a user by auto-generated ID.
     * @param items The items to load.
     * @param key The item to use as a key. This should be an auto-generated ID, since no
     * other field is unique.
     */
    public void loadUser(Item[] items, Item key) {
        userTable.load(items, new Item[] {key}, MySQLAccessor.AND);
    }

    /**
     * Loads a list of students of a class, given its auto-generated class ID.
     * @param classid The auto-generated ID of the class whose students are to be loaded.
     * @return An array of students attending the specified class.
     */
    public UserContainer[] loadStudentsInClass(Item classid) {
        UserContainer scs[];
        StringItem studentAssignedID = new StringItem(USRASSIGNEDID, "", false);
        String queryString =
            "SELECT " + ATTENDSTABLE + "." + studentAssignedID.getFieldName() + ", " + USRLNAME
                + " FROM " + ATTENDSTABLE + ", " + USERTABLE
                + " WHERE " + classid.getFieldName() + " = '" + classid.toString()
                + "' AND " + ATTENDSTABLE + "." + studentAssignedID.getFieldName() + " = " + USERTABLE + "." + studentAssignedID.getFieldName()
                + " ORDER BY " + USRLNAME
                + ";";
        ArrayList al =
            attendsTable.performDatabaseQuery(queryString, new Item[] { studentAssignedID });

        scs = new UserContainer[al.size()];

        for (int i = 0; i < al.size(); i++) {
            scs[i] =
//                new PairedUserContainer((String) ((Hashtable) al.get(i)).get(studentAssignedID.getFieldName()));
                PairedUserContainer.getPairedContainer((String) ((Hashtable) al.get(i)).get(studentAssignedID.getFieldName()), unprefixedDataSource);
        }

        return scs;
    }

    /**
     * Loads a list of parents of a given student.
     * @param assignedID The auto-generated user ID of the student whose parents are to be loaded.
     * @return The parents of the specified student.
     */
    public UserContainer[] loadParentsOfStudent(IntegerItem assignedID) {
        UserContainer ucs[];
        IntegerItem parentAssignedID =
            new IntegerItem(PARENTUSRASSIGNEDID, "", false);
        String queryString =
            "SELECT " + parentAssignedID.getFieldName()
                + " FROM " + RELATESTOTABLE
                + " WHERE " + STUDENTUSRASSIGNEDID + " = " + assignedID;
        ArrayList al =
            relatesToTable.performDatabaseQuery(queryString, new Item[] { parentAssignedID });

        ucs = new UserContainer[al.size()];

        for (int i = 0; i < al.size(); i++) {
            ucs[i] =
//          new PairedUserContainer((String) ((Hashtable) al.get(i)).get(parentAssignedID.getFieldName()));
            PairedUserContainer.getPairedContainer((String) ((Hashtable) al.get(i)).get(parentAssignedID.getFieldName()), unprefixedDataSource);
        }

        return ucs;
    }

    /**
     * Loads a list of students who attend a given class, and the parents of those students.
     * @param classid The auto-generated class ID of the class of interest.
     * @return The two-dimensional array containing students and their parents. Each 'row' in the
     * array starts with a student and is followed by all parents of the student.
     */
    public UserContainer[][] loadRecipientList(Item classid) {
        UserContainer studentContainers[] = loadStudentsInClass(classid);
        UserContainer returnVal[][] = new UserContainer[studentContainers.length][];
        UserContainer parentContainers[];

        for (int i = 0; i < studentContainers.length; i++) {
            parentContainers =
                loadParentsOfStudent(studentContainers[i].getAssignedID());
            returnVal[i] = new UserContainer[parentContainers.length + 1];
            returnVal[i][0] = studentContainers[i];
            for (int j = 0; j < parentContainers.length; j++) {
                returnVal[i][j + 1] = parentContainers[j];
            }
        }

        return returnVal;
    }

    /**
     * Loads a school by auto-generated ID.
     * @param items The items to load.
     * @param assignedID The item to use as a key. This should be an auto-generated ID, since no
     * other field is unique.
     */
    public void loadSchool(Item[] items, Item assignedID) {
        schoolTable.load(items, new Item[] { assignedID }, MySQLAccessor.AND);
    }

    /**
     * Loads a class by auto-generated ID.
     * @param items The items to load.
     * @param assignedID The item to use as a key. This should be an auto-generated ID, since no
     * other field is unique.
     */
    public void loadClass(Item[] items, Item assignedID) {
        classTable.load(items, new Item[] { assignedID }, MySQLAccessor.AND);
    }

    /**
     * Loads a class by auto-generated ID.
     * @param items The items to load.
     * @param assignedID The item to use as a key. This should be an auto-generated ID, since no
     * other field is unique.
     */
    public void loadTerm(Item[] items, Item key) {
        termTable.load(items, new Item[] {key}, MySQLAccessor.AND);
    }

    /**
     * Loads a course by auto-generated ID.
     * @param items The items to load.
     * @param assignedID The item to use as a key. This should be an auto-generated ID, since no
     * other field is unique.
     */
    public void loadCourse(Item[] items, Item assignedID) {
        courseTable.load(items, new Item[] { assignedID }, MySQLAccessor.AND);
    }

    /**
     *
     *
     *
     */
     public void loadPeriod(PeriodContainer pc) {
        String queryString =
            "SELECT *"
                + " FROM " + PERIODTABLE
                + " WHERE " + pc.getPeriodName().getFieldName() + " = '" + pc.getPeriodName() + "';";
        ArrayList al =
            periodTable.performDatabaseQuery(queryString, new Item[] { pc.getPeriodName() });

        for (int i = 0; i < al.size(); i++) {
            HourContainer hc = new HourContainer();
            hc.getDayOfWeek().setValue((String) ((Hashtable) al.get(i)).get(hc.getDayOfWeek().getFieldName()));
            hc.getPeriodStartTime().setValue((String) ((Hashtable) al.get(i)).get(hc.getPeriodStartTime().getFieldName()));
            hc.getPeriodEndTime().setValue((String) ((Hashtable) al.get(i)).get(hc.getPeriodEndTime().getFieldName()));
            pc.addHour(hc);
        }

     }

    /**
     *
     *
     *
     */
     public PeriodContainer[] loadPeriodList() {

         StringItem target = new StringItem(PERIODNAME, "", false);
         String queryString =
             "SELECT DISTINCT " + target.getFieldName() + " FROM " + PERIODTABLE + ";";
         ArrayList al = periodTable.performDatabaseQuery(queryString, new Item[] {target});
         PeriodContainer hcs[] = new PeriodContainer[al.size()];

         for (int i = 0; i < al.size(); i++) {
             hcs[i] = PeriodContainer.getPeriodContainer((String) ((Hashtable) al.get(i)).get(target.getFieldName()), this.unprefixedDataSource);
         }
         return hcs;

     }

    /**
     * Loads a message by a set of keys.
     * @param mc The container containing the items to load.
     * @param keys The items to use as keys that exist in the message table.
     */
    public void loadMessage(MessageContainer mc, Item keys[]) {
        messageTable.load(mc.getValidationItems(), keys, MySQLAccessor.AND);
    }

    /**
     * Loads a contact by a set of keys and the message it refers to. This does
     * NOT load the corresponding message.
     * @param cc The container containing the items to load.
     * @param keys The items to use as keys that exist in the contact table.
     */
    public void loadContact(ContactContainer cc, Item keys[]) {
        contactsTable.load(cc.getValidationItems(), keys, MySQLAccessor.AND);
//        loadMessage(cc.getMessage(), new Item[] { cc.getMessageAssignedID()});
    }

    /**
     * Loads a homework and any associated attachments by a homework container.
     * @param hc The homework container to load, and that contains the auto-generated ID.
     */
    public void loadHomework(HomeworkContainer hc) {
        homeworkTable.load(hc.getValidationItems(), new Item[] {hc.getHomeworkID()}, MySQLAccessor.AND);
        AttachmentContainer ac[] = this.loadHomeworkAttachment(hc.getHomeworkID());
        for (int i = 0; i < ac.length; i++) {
            hc.addAttachment(ac[i]);
        }
    }

    public void loadHomeworkSubmission(SubmissionContainer sc) {
        submissionTable.load(sc.getValidationItems(), new Item[] { sc.getAssignedHomeworkID(), sc.getAssignedStudentID(), sc.getAssignedClassID()}, MySQLAccessor.AND);
    }

//    public boolean homeworkSubmissionExists(String studentID, String homeworkID) {
//
//    }

    /**
     * Loads attachment items by a set of keys.
     * @param items A set of items to load that exist in the attachment table.
     * @param keys The items to use as keys that exist in the attachment table.
     */
    public void loadAttachment(Item[] items, Item keys[]) {
        attachmentTable.load(items, keys, MySQLAccessor.AND);
    }

    /**
     * Loads grade items by a set of keys.
     * @param items A set of items to load that exist in the grade table.
     * @param keys The items to use as keys that exist in the grade table.
     */
    public void loadGrade(Item[] items, Item keys[]) {
        gradeTable.load(items, keys, MySQLAccessor.AND);
    }

    /**
     * Loads attendance items by a set of keys.
     * @param items A set of items to load that exist in the attendance table.
     * @param keys The items to use as keys that exist in the attendance table.
     */
     public void loadAttendance(Item[] items, Item keys[]) {
         attendanceTable.load(items, keys, MySQLAccessor.AND);
     }


    /**
     * Loads the attendance related to a class and date.
     * @param classAssignedID The auto-generated ID of the class whose attendance is to be loaded.
     * @param date The MMDDYYYY date for which to load attendance info.
     * @return The attendance info associated with the specified class and date. The student ID is used as a key.
     */
    public Hashtable loadClassAttendance(Item classAssignedID, Item date) {
        Hashtable returnHash = new Hashtable();
        StringItem studentID = new StringItem(USRASSIGNEDID, "", false);
        String queryString =
            "SELECT " + studentID.getFieldName()
                + " FROM " + ATTENDANCETABLE
                + " WHERE " + classAssignedID.getFieldName() + " = " + classAssignedID.getDBFormattedString(attendanceTable)
                + " AND " + date.getFieldName() + " = " + date.getDBFormattedString(attendanceTable)
                + ";";
        ArrayList al =
            attendanceTable.performDatabaseQuery(queryString, new Item[] { studentID });

        String id;
        for (int i = 0; i < al.size(); i++) {
            id = (String) ((Hashtable) al.get(i)).get(studentID.getFieldName());
            SchoolLogger.logger.log(Level.INFO, ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>date.toString(): " + date.toString());
            returnHash.put(id, AttendanceContainer.getAttendanceContainer(id, classAssignedID.toString(), date.toString(), unprefixedDataSource));
        }

        return returnHash;
    }

    public AttendanceContainer[] loadStudentClassAttendance(Item classAssignedID, Item studentAssignedID) {
        ArrayList returnVal = new ArrayList();
        StringItem date = new StringItem(ATNDDATE, "", false);
        String queryString =
            "SELECT " + date.getFieldName()
                + " FROM " + ATTENDANCETABLE
                + " WHERE " + classAssignedID.getFieldName() + " = " + classAssignedID.getDBFormattedString(attendanceTable)
                + " AND " + studentAssignedID.getFieldName() + " = " + studentAssignedID.getDBFormattedString(attendanceTable)
                + " ORDER BY " + date.getFieldName()
                + ";";
        ArrayList al =
            attendanceTable.performDatabaseQuery(queryString, new Item[] { date });

        String dateString;
        for (int i = 0; i < al.size(); i++) {
            dateString = (String) ((Hashtable) al.get(i)).get(date.getFieldName());
            try{
                dateString = Types.sdfMMDDYYYYslash.format(Types.sdfYYYYMMDD.parse(dateString));
            }
            catch(ParseException pe){
                SchoolLogger.logger.log(Level.INFO, "ParseExpection: " + pe);
            }
        SchoolLogger.logger.log(Level.INFO, "dateString: " + dateString);
            returnVal.add(AttendanceContainer.getAttendanceContainer(studentAssignedID.toString(), classAssignedID.toString(), dateString, unprefixedDataSource));
        }

        return (AttendanceContainer[]) returnVal.toArray(new AttendanceContainer[al.size()]);
    }


    /**
     * Gets the auto-generated ID of a user given the user's school-assigned ID and user type.
     * @param username The school-assigned ID of the user.
     * @param type The type of user.
     * @return The auto-generated ID of the user.
     */
    public String getAssignedID(String username, String type) {
        ArrayList results = null;
        String returnVal = null;
        String queryString =
            "SELECT " + USRASSIGNEDID
                + " FROM " + USERTABLE
                + " WHERE " + USRSCHOOLID + " = '" + username + "'"
                + " AND " + USRTYPE + " = '" + type + "'"
                + ";";
        SchoolLogger.logger.log(Level.INFO, " - Query: " + queryString);
        results = userTable.performDatabaseQuery(queryString, new Item[] {new StringItem(USRASSIGNEDID, "", false)});
        if (results != null && results.size() > 0) {
            returnVal = (String) ((Hashtable) results.get(0)).get(USRASSIGNEDID);
        }
        return returnVal;
    }

    /**
     * Loads the homework related to a class.
     * @param classAssignedID The auto-generated ID of the class whose homework is to be loaded.
     * @return The homework associated with the specified class.
     */
    public HomeworkContainer[] loadTeacherClassHomework(Item classAssignedID) {
        HomeworkContainer hc[];
        StringItem homeworkID = new StringItem(HWASSIGNEDID, "", false);
        String queryString =
            "SELECT " + homeworkID.getFieldName()
                + " FROM " + HOMEWORKTABLE
                + " WHERE " + classAssignedID.getFieldName() + " = '" + classAssignedID.toString() + "'"
                + " ORDER BY " + HWDATEASSIGNED + " DESC"
                + ";";
        ArrayList al =
            homeworkTable.performDatabaseQuery(queryString, new Item[] { homeworkID });

        hc = new HomeworkContainer[al.size()];

        for (int i = 0; i < al.size(); i++) {
            hc[i] =
//                new HomeworkContainer((String) ((Hashtable) al.get(i)).get(homeworkID.getFieldName()), classAssignedID.toString());
                HomeworkContainer.getHomeworkContainer((String) ((Hashtable) al.get(i)).get(homeworkID.getFieldName()), unprefixedDataSource);
        }

        return hc;
    }

    /**
     * Loads the homework related to a class.
     * @param classAssignedID The auto-generated ID of the class whose homework is to be loaded.
     * @return The homework associated with the specified class.
     */
    public HomeworkContainer[] loadStudentClassHomework(Item classAssignedID) {
        HomeworkContainer hc[];
        StringItem homeworkID = new StringItem(HWASSIGNEDID, "", false);
        String queryString =
            "SELECT " + homeworkID.getFieldName()
                + " FROM " + HOMEWORKTABLE
                + " WHERE " + classAssignedID.getFieldName() + " = '" + classAssignedID.toString() + "'"
                + " AND " + HWDATEASSIGNED + " <= CURDATE() "
                + " ORDER BY " + HWDATEUPDATED + " DESC"
                + ";";
        ArrayList al =
            homeworkTable.performDatabaseQuery(queryString, new Item[] { homeworkID });

        hc = new HomeworkContainer[al.size()];

        for (int i = 0; i < al.size(); i++) {
            hc[i] =
//                new HomeworkContainer((String) ((Hashtable) al.get(i)).get(homeworkID.getFieldName()), classAssignedID.toString());
                HomeworkContainer.getHomeworkContainer((String) ((Hashtable) al.get(i)).get(homeworkID.getFieldName()), unprefixedDataSource);
        }

        return hc;
    }

    /**
     * Loads the homework related to a class.
     * @param classAssignedID The auto-generated ID of the class whose homework is to be loaded.
     * @return The homework associated with the specified class.
     */
    public HomeworkContainer[] loadTodaysHomework(Item studentAssignedID) {
        HomeworkContainer hc[];
        java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
//        java.util.Date today = new java.util.Date();
        java.util.GregorianCalendar today = new java.util.GregorianCalendar();
        StringItem homeworkID = new StringItem(HWASSIGNEDID, "", false);
        String queryString =
            "SELECT " + HOMEWORKTABLE + "." + homeworkID.getFieldName()
                + " FROM " + HOMEWORKTABLE + ", " + ATTENDSTABLE
                + " WHERE " + ATTENDSTABLE + "." + studentAssignedID.getFieldName() + " = '" + studentAssignedID.toString() + "'"
                + " AND " + ATTENDSTABLE + "." + CLASSASSIGNEDID + " = " + HOMEWORKTABLE + "." + CLASSASSIGNEDID
                + " AND LEFT(" + HOMEWORKTABLE + "." + HWDATEASSIGNED + ", 10) = '"+ sdf.format(today.getTime()) + "'"
                + " ORDER BY " + HWDATEASSIGNED + " DESC"
                + ";";
        ArrayList al =
            homeworkTable.performDatabaseQuery(queryString, new Item[] { homeworkID });

        hc = new HomeworkContainer[al.size()];

        for (int i = 0; i < al.size(); i++) {
            hc[i] =
//                new HomeworkContainer((String) ((Hashtable) al.get(i)).get(homeworkID.getFieldName()), classAssignedID.toString());
                HomeworkContainer.getHomeworkContainer((String) ((Hashtable) al.get(i)).get(homeworkID.getFieldName()), unprefixedDataSource);
        }

        return hc;
    }

    /**
     * Loads the homework related to a class.
     * @param classAssignedID The auto-generated ID of the class whose homework is to be loaded.
     * @return The homework associated with the specified class.
     */
    public HomeworkContainer[] loadTodaysTeacherHomework(Item teacherAssignedID) {
        HomeworkContainer hc[];
        java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
//        java.util.Date today = new java.util.Date();
        java.util.GregorianCalendar today = new java.util.GregorianCalendar();
        StringItem homeworkID = new StringItem(HWASSIGNEDID, "", false);
        String queryString =
            "SELECT " + HOMEWORKTABLE + "." + homeworkID.getFieldName()
                + " FROM " + HOMEWORKTABLE + ", " + TEACHESTABLE
                + " WHERE " + TEACHESTABLE + "." + teacherAssignedID.getFieldName() + " = '" + teacherAssignedID.toString() + "'"
                + " AND " + TEACHESTABLE + "." + CLASSASSIGNEDID + " = " + HOMEWORKTABLE + "." + CLASSASSIGNEDID
                + " AND LEFT(" + HOMEWORKTABLE + "." + HWDATEASSIGNED + ", 10) = '"+ sdf.format(today.getTime()) + "'"
                + " ORDER BY " + HWDATEASSIGNED + " DESC"
                + ";";
        ArrayList al =
            homeworkTable.performDatabaseQuery(queryString, new Item[] { homeworkID });

        hc = new HomeworkContainer[al.size()];

        for (int i = 0; i < al.size(); i++) {
            hc[i] =
//                new HomeworkContainer((String) ((Hashtable) al.get(i)).get(homeworkID.getFieldName()), classAssignedID.toString());
                HomeworkContainer.getHomeworkContainer((String) ((Hashtable) al.get(i)).get(homeworkID.getFieldName()), unprefixedDataSource);
        }

        return hc;
    }

    /**
     * Loads the attachments associated with a homework.
     * @param hwAssignedID The auto-generated ID of the homework whose attachments are to be loaded.
     * @return The attachments associated with the specified homework.
     */
    public AttachmentContainer[] loadHomeworkAttachment(Item hwAssignedID) {
        AttachmentContainer ac[];
        StringItem attachmentID = new StringItem(ATTASSIGNEDID, "", false);
        String queryString =
            "SELECT " + attachmentID.getFieldName()
                + " FROM " + HWINCLUDESTABLE
                + " WHERE " + hwAssignedID.getFieldName() + " = '" + hwAssignedID.toString()
                + "';";
        ArrayList al = attachmentTable.performDatabaseQuery(queryString, new Item[] { attachmentID });

        ac = new AttachmentContainer[al.size()];

        for (int i = 0; i < al.size(); i++) {
            ac[i] =
                AttachmentContainer.getAttachmentContainer((String) ((Hashtable) al.get(i)).get(attachmentID.getFieldName()), unprefixedDataSource);
        }

        return ac;
    }

    /**
     * Loads the classes that a student attends.
     * @param key The auto-generated ID of the student whose classes are to be loaded.
     * @return The classes the specified student attends.
     */
    public ClassContainer[] loadStudentClasses(Item key) {
        ClassContainer cc[];
        StringItem classid = new StringItem(CLASSASSIGNEDID, "", false);
        String queryString =
            "SELECT " + classid.getFieldName()
                + " FROM " + ATTENDSTABLE
                + " WHERE " + key.getFieldName() + " = '" + key.toString()
                + "';";
        ArrayList al =
            attendsTable.performDatabaseQuery(queryString, new Item[] { classid });

        cc = new ClassContainer[al.size()];

        for (int i = 0; i < al.size(); i++) {
            cc[i] =
//          new ClassContainer((String) ((Hashtable) al.get(i)).get(classid.getFieldName()));
            ClassContainer.getClassContainer((String) ((Hashtable) al.get(i)).get(classid.getFieldName()), unprefixedDataSource);
        }

        return cc;
    }

    /**
     * Loads the classes a teacher teaches.
     * @param teacherAssignedID The auto-generated ID of the teacher whose classes are to be loaded.
     * @return The classes the specifeid teacher teaches.
     */
    public ClassContainer[] loadTeacherClasses(Item teacherAssignedID) {
        ClassContainer cc[];
        StringItem classid = new StringItem(CLASSASSIGNEDID, "", false);
        String queryString =
            "SELECT " + classid.getFieldName()
                + " FROM " + TEACHESTABLE
                + " WHERE " + teacherAssignedID.getFieldName() + " = '" + teacherAssignedID.toString()
                + "';";
        ArrayList al =
            teachesTable.performDatabaseQuery(queryString, new Item[] { classid });

        cc = new ClassContainer[al.size()];

        for (int i = 0; i < al.size(); i++) {
            cc[i] =
//          new ClassContainer((String) ((Hashtable) al.get(i)).get(classid.getFieldName()));
            ClassContainer.getClassContainer((String) ((Hashtable) al.get(i)).get(classid.getFieldName()), unprefixedDataSource);
        }

        return cc;
    }

    // TODO need to add a loadAnnouncements method

    /**
     * Loads contacts associated with a user and a particular class.
     * @param assignedIDString The auto-generated ID of the user of interest.
     * @param classIDString The auto-generated ID of the class of interest.
     * @return The contacts for the user and for the class.
     */
    public ContactContainer[] loadContacts(String assignedIDString, String classIDString) {
        ContactContainer cc[];
        StringItem toID = new StringItem(CTTOID, assignedIDString, false);
        StringItem msgID = new StringItem(MSGASSIGNEDID, "", false);
        StringItem fromID = new StringItem(CTFROMID, "", false);
        StringItem classID =
            new StringItem(CLASSASSIGNEDID, classIDString, false);
        DateItem dateSent = new DateItem(MSGTIMESENT, "", false);
        String queryString =
            "SELECT " + CONTACTSTABLE + "." + msgID.getFieldName() + ", " + toID.getFieldName() + ", " + fromID.getFieldName()
                + " FROM " + CONTACTSTABLE + ", " + MESSAGETABLE
                + " WHERE " + CONTACTSTABLE + "." + msgID.getFieldName() + " = " + MESSAGETABLE + "." + msgID.getFieldName()
                + " AND " + CLASSASSIGNEDID + " = " + classID.getDBFormattedString(contactsTable)
                + " AND " + toID.getFieldName() + " = " + toID.getDBFormattedString(contactsTable)
                + " ORDER BY " + dateSent.getFieldName() + " DESC"
                + ";";

        ArrayList al =
            contactsTable.performDatabaseQuery(queryString, new Item[] { msgID, toID, fromID });

        cc = new ContactContainer[al.size()];
        for (int i = 0; i < al.size(); i++) {

            Hashtable result = (Hashtable) al.get(i);
            toID.setValue((String) result.get(toID.getFieldName()));
            msgID.setValue((String) result.get(msgID.getFieldName()));
            fromID.setValue((String) result.get(fromID.getFieldName()));
            cc[i] =
                ContactContainer.getContactContainer(msgID.toString(), toID.toString(), fromID.toString(), unprefixedDataSource);
        }

        return cc;
    }

    /**
     * Loads contacts associated with a user and a particular class.
     * @param classIDString The auto-generated ID of the class of interest.
     * @return The contacts for the user and for the class.
     */
    public ContactContainer[] loadTodaysContacts(String assignedIDString) {
        ContactContainer cc[];
        java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
        java.util.GregorianCalendar today = new java.util.GregorianCalendar();
        StringItem toID = new StringItem(CTTOID, assignedIDString, false);
        StringItem msgID = new StringItem(MSGASSIGNEDID, "", false);
        StringItem fromID = new StringItem(CTFROMID, "", false);
        DateItem dateSent = new DateItem(MSGTIMESENT, "", false);
        String queryString =
            "SELECT " + CONTACTSTABLE + "." + msgID.getFieldName() + ", " + toID.getFieldName() + ", " + fromID.getFieldName()
                + " FROM " + CONTACTSTABLE + ", " + MESSAGETABLE
                + " WHERE " + CONTACTSTABLE + "." + msgID.getFieldName() + " = " + MESSAGETABLE + "." + msgID.getFieldName()
                + " AND " + toID.getFieldName() + " = " + toID.getDBFormattedString(contactsTable)
                + " AND LEFT(" + MESSAGETABLE + "." + MSGTIMESENT + ", 10) = '"+ sdf.format(today.getTime()) + "'"
                + " ORDER BY " + dateSent.getFieldName() + " DESC"
                + ";";

        ArrayList al =
            contactsTable.performDatabaseQuery(queryString, new Item[] { msgID, toID, fromID });

        cc = new ContactContainer[al.size()];
        for (int i = 0; i < al.size(); i++) {

            Hashtable result = (Hashtable) al.get(i);
            toID.setValue((String) result.get(toID.getFieldName()));
            msgID.setValue((String) result.get(msgID.getFieldName()));
            fromID.setValue((String) result.get(fromID.getFieldName()));
            cc[i] =
                ContactContainer.getContactContainer(msgID.toString(), toID.toString(), fromID.toString(), unprefixedDataSource);
        }

        return cc;
    }

    /**
     * Gets the auto-generated ID of a teacher of a class.
     * TODO this should be modified to return a list of users.
     * @param classid The auto-generated ID of the class whose teacher ID should be retrieved.
     * @return The auto-generated ID of the teacher who teacher the specified class.
     */
    public Item getClassTeacherID(Item classid) {
        StringItem returnItem = new StringItem(USRASSIGNEDID, "", true);
        teachesTable.load(new Item[] { returnItem }, new Item[] { classid }, MySQLAccessor.AND);
        return returnItem;
    }

    /**
     * Saves a user. This does not save the user's password.
     * @param uc The user container to save.
     */
    synchronized public void save(UserContainer uc) {
        userTable.save(uc.getValidationItems(), new Item[] { uc.getAssignedID()}, DBSQLAccessor.AND);
        if ("-1".equals(uc.getAssignedID().toString())) {
            userTable.getLastID(uc.getAssignedID());
        }
    }

    /**
     * Saves attendance.
     * @param ac The attendance container to save.
     */
    synchronized public void save(AttendanceContainer ac) {
        attendanceTable.save(
            ac.getValidationItems(),
            new Item[] { ac.getStudentID(), ac.getClassID(), ac.getDate()},
            DBSQLAccessor.AND);
    }

    /**
     * Saves a class.
     * @param cc The class container to save.
     */
    synchronized public void save(ClassContainer cc) {
        classTable.save(cc.getValidationItems(), new Item[] { cc.getClassAssignedID()}, DBSQLAccessor.AND);
        if ("-1".equals(cc.getClassAssignedID().toString())) {
            classTable.getLastID(cc.getClassAssignedID());
        }
        save(cc.getAssociatedCourse());
    }

    /**
     * Saves a term.
     * @param tc The term container to save.
     */
    synchronized public void save(TermContainer tc) {
        termTable.save(tc.getValidationItems(), new Item[] {tc.getTermAssignedId()}, DBSQLAccessor.AND);
    }

    /**
     * Saves a course.
     * @param cc The course container to save.
     */
    synchronized public void save(CourseContainer cc) {
        courseTable.save(cc.getValidationItems(), new Item[] { cc.getCourseAssignedID()}, DBSQLAccessor.AND);
        if ("-1".equals(cc.getCourseAssignedID().toString())) {
            courseTable.getLastID(cc.getCourseAssignedID());
        }
    }

    /**
     * Saves a homework and any associated attachments.
     * @param hc The homework container to save.
     */
    synchronized public void save(HomeworkContainer hc) {
        homeworkTable.save(hc.getValidationItems(), new Item[] { hc.getHomeworkID(), hc.getClassID()}, DBSQLAccessor.AND);
        if ("-1".equals(hc.getHomeworkID().toString())) {
            homeworkTable.getLastID(hc.getHomeworkID());
        }
        AttachmentContainer ac[] = hc.getAttachments();
        for (int i = 0; i < ac.length; i++) {
            save(ac[i]);
            saveHWInclude(hc.getHomeworkID().toString(), ac[i].getAssignedAttachmentID().toString());
        }

    }

    /**
     * Saves an attachment.
     * @param ac The attachment container to save.
     */
    synchronized public void save(AttachmentContainer ac) {
        attachmentTable.save(ac.getValidationItems(), new Item[] { ac.getAssignedAttachmentID()}, DBSQLAccessor.AND);
        if ("-1".equals(ac.getAssignedAttachmentID().toString())) {
            attachmentTable.getLastID(ac.getAssignedAttachmentID());
        }
    }

    /**
     * Saves a user. This does not save the user's password.
     * @param uc The user container to save.
     */
    synchronized public void save(SubmissionContainer sc) {
        submissionTable.save(sc.getValidationItems(), new Item[] { sc.getAssignedClassID(), sc.getAssignedHomeworkID(), sc.getAssignedStudentID()}, DBSQLAccessor.AND);
    }

    /**
     * Saves a contact.
     * @param cc The contact container to save.
     */
    synchronized public void save(ContactContainer cc) {
        contactsTable.save(cc.getValidationItems(), new Item[] {cc.getMessageAssignedID(), cc.getToID(), cc.getFromID()}, DBSQLAccessor.AND);
        save(cc.getMessage());
    }

    /**
     * Saves a message.
     * @param mc The message container to save.
     */
    synchronized public void save(MessageContainer mc) {
        messageTable.save(mc.getValidationItems(), new Item[] {mc.getAssignedMessageID()}, DBSQLAccessor.AND);
        if ("-1".equals(mc.getAssignedMessageID().toString())) {
            SchoolLogger.logger.log(Level.INFO, " updating message ID: ");
            messageTable.getLastID(mc.getAssignedMessageID());
        }
    }

    /**
     * Saves a grade.
     * @param gc The grade container to save.
     */
    synchronized public void save(GradeContainer gc) {
        gradeTable.save(
            gc.getValidationItems(),
            new Item[] { gc.getClassID(), gc.getHomeworkID(), gc.getStudentID()},
            DBSQLAccessor.AND);
    }

    /**
     * Saves the relationship between a student and a parent.
     * @param parentAssignedID The auto-generated ID of the parent.
     * @param studentAssignedID The auto-generated ID of the student.
     */
    synchronized public void saveRelationship(String parentAssignedID, String studentAssignedID) {
        StringItem pid =
            new StringItem(PARENTUSRASSIGNEDID, parentAssignedID, false);
        StringItem sid =
            new StringItem(STUDENTUSRASSIGNEDID, studentAssignedID, false);
        relatesToTable.save(new Item[] { pid, sid }, new Item[] { pid, sid }, DBSQLAccessor.AND);
    }

    /**
     * Saves the relationship between a homework and an attachment.
     * @param homeworkID The auto-generated ID of the homework.
     * @param attachmentID The auto-generated ID of the attachment.
     */
    synchronized public void saveHWInclude(String homeworkID, String attachmentID) {
        StringItem hwid = new StringItem(HWASSIGNEDID, homeworkID, false);
        StringItem atid = new StringItem(ATTASSIGNEDID, attachmentID, false);
        hwIncludesTable.save(new Item[] { hwid, atid }, new Item[] { hwid, atid }, DBSQLAccessor.AND);
    }

    /**
     * Saves a user and the user's password.
     * @param sc The user container to save.
     * @param pi The password to save.
     */
    synchronized public void save(UserContainer uc, PasswordItem pi) {
    	uc.getFailures().setValue("0");
		
        Item items[] = uc.getValidationItems();
        ArrayList al = new ArrayList(items.length + 2);
        for (int i = 0; i < items.length; i++) {
            al.add(items[i]);
        }

        if (pi != null && !pi.isEmpty()) {
            al.add(pi);
        }
        userTable.save((Item[]) al.toArray(new Item[al.size()]), new Item[] { uc.getAssignedID()}, DBSQLAccessor.AND);
        if ("-1".equals(uc.getAssignedID().toString())) {
            userTable.getLastID(uc.getAssignedID());
        }
    }

    /**
     * Loads a list of student information suitable to be displayed in the list of students in
     * the administrative editor.
     * @return A list of student information containing the auto-generated ID, the school-assigned ID,
     * the first name, and the last name.
     */
    public Item[][] loadEditorStudentList() {
        Item returnItems[][];
        StringItem assignedID = new StringItem(USRASSIGNEDID, "", false);
        StringItem schoolID = new StringItem(USRSCHOOLID, "", false);
        StringItem first = new StringItem(USRFNAME, "", false);
        StringItem last = new StringItem(USRLNAME, "", false);
        String queryString =
            "SELECT " + assignedID.getFieldName() + ", " + schoolID.getFieldName() + ", " + first.getFieldName() + ", " + last.getFieldName()
                + " FROM " + USERTABLE
                + " WHERE " + USRTYPE + " = " + "'S'"
                + " ORDER BY " + last.getFieldName() + ", " + first.getFieldName()
                + ";";
        ArrayList al =
            userTable.performDatabaseQuery(queryString, new Item[] { assignedID, schoolID, first, last });

        returnItems = new Item[al.size()][4];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] =
                new StringItem(assignedID.getFieldName(), (String) result.get(assignedID.getFieldName()), false);
            returnItems[i][1] =
                new StringItem(schoolID.getFieldName(), (String) result.get(schoolID.getFieldName()), false);
            returnItems[i][2] =
                new StringItem(first.getFieldName(), (String) result.get(first.getFieldName()), false);
            returnItems[i][3] =
                new StringItem(last.getFieldName(), (String) result.get(last.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads information regarding the children of a given parent.
     * @param thisAssignedID The auto-generated ID of the parent whose children's information
     * should be loaded.
     * @return The child's auto-generated ID, first name, and last name.
     */
    public Item[][] loadParentsChildren(String thisAssignedID) {
        Item returnItems[][];
        StringItem pAssignedID = new StringItem(USRASSIGNEDID, thisAssignedID, false);
        StringItem sAssignedID = new StringItem(USRASSIGNEDID, "", false);
        StringItem studentFirstName = new StringItem(USRFNAME, "", false);
        StringItem studentLastName = new StringItem(USRLNAME, "", false);

        String queryString = "SELECT " + sAssignedID.getFieldName().toString() + ", "
                                + studentFirstName.getFieldName().toString() + ", "
                                + studentLastName.getFieldName().toString()
                                + " FROM " + USERTABLE + ", " + RELATESTOTABLE
                                + " WHERE " + PARENTUSRASSIGNEDID + " = " + pAssignedID.getDBFormattedString(relatesToTable)
                                + " AND " + STUDENTUSRASSIGNEDID + " = " + USERTABLE + "." + sAssignedID.getFieldName().toString()
                                + " ORDER BY " + studentLastName.getFieldName().toString() + ", " + studentFirstName.getFieldName().toString() + ";";

        ArrayList al = userTable.performDatabaseQuery(queryString, new Item[] {sAssignedID, studentFirstName, studentLastName});

        returnItems = new Item[al.size()][3];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] = new StringItem(sAssignedID.getFieldName().toString(), (String) result.get(sAssignedID.getFieldName()), false);
            returnItems[i][1] = new StringItem(studentFirstName.getFieldName().toString(), (String) result.get(studentFirstName.getFieldName()), false);
            returnItems[i][2] = new StringItem(studentLastName.getFieldName().toString(), (String) result.get(studentLastName.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads information regarding the parents of a given student.
     * @param thisAssignedID The auto-generated ID of the student whose parent's information
     * is to be loaded.
     * @return The parents' auto-generated ID, first name, and last name.
     */
    public Item[][] loadChildrensParents(String thisAssignedID) {
        Item returnItems[][];
        StringItem sAssignedID = new StringItem(USRASSIGNEDID, thisAssignedID, false);
        StringItem pAssignedID = new StringItem(USRASSIGNEDID, "", false);
        StringItem parentFirstName = new StringItem(USRFNAME, "", false);
        StringItem parentLastName = new StringItem(USRLNAME, "", false);

        String queryString = "SELECT " + sAssignedID.getFieldName().toString() + ", "
                                + parentFirstName.getFieldName().toString() + ", "
                                + parentLastName.getFieldName().toString()
                                + " FROM " + USERTABLE + ", " + RELATESTOTABLE
                                + " WHERE " + STUDENTUSRASSIGNEDID + " = " + sAssignedID.getDBFormattedString(relatesToTable)
                                + " AND " + PARENTUSRASSIGNEDID + " = " + USERTABLE + "." + pAssignedID.getFieldName().toString()
                                + " ORDER BY " + parentLastName.getFieldName().toString() + ", " + parentFirstName.getFieldName().toString() + ";";

        ArrayList al = userTable.performDatabaseQuery(queryString, new Item[] {sAssignedID, parentFirstName, parentLastName});

        returnItems = new Item[al.size()][3];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] = new StringItem(pAssignedID.getFieldName().toString(), (String) result.get(pAssignedID.getFieldName()), false);
            returnItems[i][1] = new StringItem(parentFirstName.getFieldName().toString(), (String) result.get(parentFirstName.getFieldName()), false);
            returnItems[i][2] = new StringItem(parentLastName.getFieldName().toString(), (String) result.get(parentLastName.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads a list of student information suitable to be displayed in the list of students in
     * the administrative editor.
     * @return A list of student information containing the auto-generated ID,
     * the first name, and the last name.
     * TODO this should be replaced by loadEditorStudentList
     */
    public Item[][] loadAvailadbleChildren() {
        Item returnItems[][];
        StringItem sType = new StringItem(USRTYPE, STUDENT, false);
        StringItem sAssignedID = new StringItem(USRASSIGNEDID, "", false);
        StringItem studentFirstName = new StringItem(USRFNAME, "", false);
        StringItem studentLastName = new StringItem(USRLNAME, "", false);

        String queryString = "SELECT " + sAssignedID.getFieldName().toString() + ", "
                                + studentFirstName.getFieldName().toString() + ", "
                                + studentLastName.getFieldName().toString()
                                + " FROM " + USERTABLE
                                + " WHERE " + sType.getFieldName().toString() + " = " + sType.getDBFormattedString(userTable)
                                + " ORDER BY " + studentLastName.getFieldName().toString() + ", " + studentFirstName.getFieldName().toString() + ";";

        ArrayList al = userTable.performDatabaseQuery(queryString, new Item[] {sAssignedID, studentFirstName, studentLastName});

        returnItems = new Item[al.size()][3];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] = new StringItem(sAssignedID.getFieldName().toString(), (String) result.get(sAssignedID.getFieldName()), false);
            returnItems[i][1] = new StringItem(studentFirstName.getFieldName().toString(), (String) result.get(studentFirstName.getFieldName()), false);
            returnItems[i][2] = new StringItem(studentLastName.getFieldName().toString(), (String) result.get(studentLastName.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads a list of parent information suitable to be displayed in the list of parents in
     * the administrative editor.
     * @return A list of parent information containing the auto-generated ID,
     * the first name, and the last name.
     */
    public Item[][] loadAvailableParents() {
        Item returnItems[][];
        StringItem pType = new StringItem(USRTYPE, PARENT, false);
        StringItem pAssignedID = new StringItem(USRASSIGNEDID, "", false);
        StringItem pFirstName = new StringItem(USRFNAME, "", false);
        StringItem pLastName = new StringItem(USRLNAME, "", false);

        String queryString = "SELECT " + pAssignedID.getFieldName().toString() + ", "
                                + pFirstName.getFieldName().toString() + ", "
                                + pLastName.getFieldName().toString()
                                + " FROM " + USERTABLE
                                + " WHERE " + pType.getFieldName().toString() + " = " + pType.getDBFormattedString(userTable)
                                + " ORDER BY " + pLastName.getFieldName().toString() + ", " + pFirstName.getFieldName().toString() + ";";

        ArrayList al = userTable.performDatabaseQuery(queryString, new Item[] {pAssignedID, pFirstName, pLastName});

        returnItems = new Item[al.size()][3];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] = new StringItem(pAssignedID.getFieldName().toString(), (String) result.get(pAssignedID.getFieldName()), false);
            returnItems[i][1] = new StringItem(pFirstName.getFieldName().toString(), (String) result.get(pFirstName.getFieldName()), false);
            returnItems[i][2] = new StringItem(pLastName.getFieldName().toString(), (String) result.get(pLastName.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads a list of user information suitable to be displayed in the list of users in
     * the administrative editor.
     * @param type The type of user to load.
     * @return A list of user information containing the auto-generated ID, school-assigned ID
     * the first name, and the last name.
     * TODO this should be used to replace the user type-specific loaders above
     */
    public Item[][] loadUserList(String type) {
        Item returnItems[][];
        StringItem assignedid = new StringItem(USRASSIGNEDID, "", false);
        StringItem schoolid = new StringItem(USRSCHOOLID, "", false);
        StringItem firstname = new StringItem(USRFNAME, "", false);
        StringItem lastname = new StringItem(USRLNAME, "", false);

        String queryString =
            "SELECT " + assignedid.getFieldName().toString() + ", " + schoolid.getFieldName().toString()
                + ", " + firstname.getFieldName().toString() + ", " + lastname.getFieldName().toString()
                + " FROM " + USERTABLE
                + " WHERE " + USRTYPE + " = " + "'" + type + "'"
                + " ORDER BY " + lastname.getFieldName() + ", " + firstname.getFieldName()
                + ";";

        ArrayList al =
            userTable.performDatabaseQuery(queryString, new Item[] { assignedid, schoolid, firstname, lastname });

        returnItems = new Item[al.size()][4];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] =
                new StringItem(USRASSIGNEDID, (String) result.get(assignedid.getFieldName()), false);
            returnItems[i][1] =
                new StringItem(USRSCHOOLID, (String) result.get(schoolid.getFieldName()), false);
            returnItems[i][2] =
                new StringItem(USRFNAME, (String) result.get(firstname.getFieldName()), false);
            returnItems[i][3] =
                new StringItem(USRLNAME, (String) result.get(lastname.getFieldName()), false);
        }

        return returnItems;

    }

    /**
     * Loads a list of user information suitable to be displayed in the list of users in
     * the administrative editor.
     * @param type The type of user to load.
     * @return A list of teacher information containing the auto-generated ID, school-assigned ID
     * the first name, and the last name.
     * TODO This should be replaced by loadUserList.
     */
    public Item[][] loadEditorTeacherList() {
        Item returnItems[][];
        StringItem assignedID = new StringItem(USRASSIGNEDID, "", false);
        StringItem schoolID = new StringItem(USRSCHOOLID, "", false);
        StringItem first = new StringItem(USRFNAME, "", false);
        StringItem last = new StringItem(USRLNAME, "", false);
        String queryString =
            "SELECT " + assignedID.getFieldName() + ", " + first.getFieldName() + ", "
                      + last.getFieldName() + ", " + schoolID.getFieldName()
                + " FROM " + USERTABLE
                + " WHERE " + USRTYPE + " = " + "'T'"
                + " ORDER BY " + last.getFieldName() + ";";
        ArrayList al =
            userTable.performDatabaseQuery(
                queryString,
                new Item[] { assignedID, schoolID, first, last });

        returnItems = new Item[al.size()][4];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] =
                new StringItem(USRASSIGNEDID, (String) result.get(assignedID.getFieldName()), false);
            returnItems[i][1] =
                new StringItem(USRSCHOOLID, (String) result.get(schoolID.getFieldName()),false);
            returnItems[i][2] =
                new StringItem(USRFNAME, (String) result.get(first.getFieldName()),false);
            returnItems[i][3] =
                new StringItem(USRLNAME, (String) result.get(last.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads a list of class information suitable to be displayed in the list of classes in
     * the administrative editor.
     * @return A list of class information containing the auto-generated ID, school-assigned ID,
     * and the time of the class.
     */
    public Item[][] loadAvailableClassList() {
        Item returnItems[][];
        StringItem assignedid = new StringItem(CLASSASSIGNEDID, "", false);
        StringItem schoolid = new StringItem(CLASSSCHOOLID, "", false);
        StringItem time = new StringItem(CLASSTIME, "", false);
        String queryString = "SELECT "+ assignedid.getFieldName() +", " + schoolid.getFieldName() + ", " +
                                    time.getFieldName() +
                                    " FROM " + CLASSTABLE +
                                    " ORDER BY " + schoolid.getFieldName() + ";";
        ArrayList al = classTable.performDatabaseQuery(queryString, new Item[] {assignedid, schoolid, time});

        returnItems = new Item[al.size()][3];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] = new StringItem(assignedid.getFieldName(), (String) result.get(assignedid.getFieldName()), false);
            returnItems[i][1] = new StringItem(schoolid.getFieldName(), (String) result.get(schoolid.getFieldName()), false);
            returnItems[i][2] = new StringItem(time.getFieldName(), (String) result.get(time.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads a list of class information suitable to be displayed in the list of classes in
     * the administrative editor.
     * @return A list of class information containing the auto-generated ID, school-assigned ID,
     * and the time of the class.
     */
    public Item[][] loadAvailableTermList() {
        Item returnItems[][];
        IntegerItem id = new IntegerItem(TERMASSIGNEDID, "", false);
        StringItem name = new StringItem(TERMNAME, "", false);
        DateNoTimeItem start = new DateNoTimeItem(TERMSTART, "", false);
        DateNoTimeItem end = new DateNoTimeItem(TERMEND, "", false);
        String queryString = "SELECT "+ id.getFieldName() +", " + name.getFieldName() +", " + 
									start.getFieldName() + ", " + end.getFieldName() +
                                    " FROM " + TERMTABLE +
                                    " ORDER BY " + start.getFieldName() + ";";
        ArrayList al = termTable.performDatabaseQuery(queryString, new Item[] {id, name, start, end});
        returnItems = new Item[al.size()][4];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] = new IntegerItem(id.getFieldName(), (String) result.get(id.getFieldName()), false);
            returnItems[i][1] = new StringItem(name.getFieldName(), (String) result.get(name.getFieldName()), false);
            returnItems[i][2] = new DateNoTimeItem(start.getFieldName(), "", false);
            returnItems[i][3] = new DateNoTimeItem(end.getFieldName(), "", false);

            returnItems[i][2].createFromDBString((String) result.get(start.getFieldName()), termTable);
            returnItems[i][3].createFromDBString((String) result.get(end.getFieldName()), termTable);
        }
        return returnItems;
    }

    /**
     * Loads summary information regarding the classes a student is attending.
     * @param studentID The auto-generated ID of the student of interest.
     * @return A list of class information including the auto-generated class ID, the
     * school-assigned class ID, and the time the class meets.
     */
    public Item[][] loadStudentAssignedClassList(Item studentID) {
        Item returnItems[][];
        StringItem assignedid = new StringItem(CLASSASSIGNEDID, "", false);
        StringItem schoolid = new StringItem(CLASSSCHOOLID, "", false);
        StringItem time = new StringItem(CLASSTIME, "", false);
        StringItem courseDesc = new StringItem(COURSESHORTNAME, "", false);

//        String qs =
//            "SELECT " + ATTENDSTABLE + "." + CLASSASSIGNEDID + ", " + schoolid.getFieldName() + ", " + time.getFieldName()
//                + " FROM " + CLASSTABLE + ", " + ATTENDSTABLE
//                + " WHERE " + CLASSTABLE + "." + CLASSASSIGNEDID + " = " + ATTENDSTABLE + "." + CLASSASSIGNEDID
//                + " AND " + ATTENDSTABLE + "." + studentID.getFieldName() + " = " + studentID.getDBFormattedString(classTable)
//                + ";";
        String qs =
            "SELECT " + COURSETABLE + "." + COURSESHORTNAME + ", " + ATTENDSTABLE + "." + CLASSASSIGNEDID + ", " + schoolid.getFieldName() + ", " + time.getFieldName()
                + " FROM " + CLASSTABLE + ", " + ATTENDSTABLE + ", " + COURSETABLE
                + " WHERE " + CLASSTABLE + "." + CLASSASSIGNEDID + " = " + ATTENDSTABLE + "." + CLASSASSIGNEDID
                + " AND " + CLASSTABLE + "." + COURSEASSIGNEDID + " = " + COURSETABLE + "." + COURSEASSIGNEDID
                + " AND " + ATTENDSTABLE + "." + studentID.getFieldName() + " = " + studentID.getDBFormattedString(classTable)
                + ";";

        ArrayList al =
            classTable.performDatabaseQuery(qs, new Item[] { assignedid, schoolid, time, courseDesc });

//        returnItems = new Item[al.size()][3];
//        returnItems = new Item[al.size()][3];
        returnItems = new Item[al.size()][4];

        for (int i = 0; i < returnItems.length; i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] =
                new StringItem(assignedid.getFieldName(), (String) result.get(assignedid.getFieldName()), false);
            returnItems[i][1] =
                new StringItem(schoolid.getFieldName(), (String) result.get(schoolid.getFieldName()), false);
            returnItems[i][2] =
                new StringItem(time.getFieldName(), (String) result.get(time.getFieldName()), false);
            returnItems[i][3] =
                new StringItem(courseDesc.getFieldName(), (String) result.get(courseDesc.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads summary information regarding the teacher(s) of a class.
     * @param classAssignedID The auto-generated class ID of interest.
     * @return A list of auto-generated IDs, first names, and last names of teachers associated with
     * the class.
     */
    public Item[][] loadTeacherFromClassID(Item classAssignedID) {
        Item returnItems[][];
        StringItem assignedID = new StringItem(USRASSIGNEDID, "", false);
        StringItem fname = new StringItem(USRFNAME, "", false);
        StringItem lname = new StringItem(USRLNAME, "", false);
        String qs =
            "SELECT " + USERTABLE + "." + assignedID.getFieldName() + ", " + USERTABLE + "." + fname.getFieldName() + ", " + lname.getFieldName()
                + " FROM " + USERTABLE + ", " + TEACHESTABLE
                + " WHERE " + TEACHESTABLE + "." + classAssignedID.getFieldName() + " = " + classAssignedID.getDBFormattedString(teachesTable)
                + " AND " + TEACHESTABLE + "." + USRASSIGNEDID + " = " + USERTABLE + "." + USRASSIGNEDID + ";";

        ArrayList al =
            classTable.performDatabaseQuery(qs, new Item[] { assignedID, fname, lname});

        returnItems = new Item[al.size()][3];

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] =
                new StringItem(assignedID.getFieldName(), (String) result.get(assignedID.getFieldName()), false);
            returnItems[i][1] =
                new StringItem(fname.getFieldName(), (String) result.get(fname.getFieldName()), false);
            returnItems[i][2] =
                new StringItem(lname.getFieldName(), (String) result.get(lname.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Gets the number of students who attend the class.
     * @param classAssignedID The auto-generated class ID of interest.
     * @return The number of student who attend the class.
     */
    public int getClassSize(Item classAssignedID) {
        IntegerItem returnVal = new IntegerItem("COUNT(*)", "", false);

        String qs =
            "SELECT " + returnVal.getFieldName()
                + " FROM " + ATTENDSTABLE
                + " WHERE " + classAssignedID.getFieldName() + " = " + classAssignedID.getDBFormattedString(attendsTable)
                + ";";

        ArrayList al =
            classTable.performDatabaseQuery(qs, new Item[] { returnVal});

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnVal =
                new IntegerItem(returnVal.getFieldName(), (String) result.get(returnVal.getFieldName()), false);
        }

        return ((Integer) returnVal.getObject()).intValue();

    }

    /**
     * Loads summary information regarding the students who attend a class.
     * @param classAssignedID The auto-generated class ID of interest.
     * @return A list of auto-generated IDs, first names, and last names of students who attend
     * the class.
     */
    public Item[][] loadClassEnrollment(Item classAssignedID) {
        Item returnItems[][];
        StringItem assignedID = new StringItem(USRASSIGNEDID, "", false);
        StringItem fname = new StringItem(USRFNAME, "", false);
        StringItem lname = new StringItem(USRLNAME, "", false);
        String qs =
            "SELECT " + USERTABLE + "." + assignedID.getFieldName() + ", " + USERTABLE + "." + fname.getFieldName() + ", " + lname.getFieldName()
                + " FROM " + USERTABLE + ", " + ATTENDSTABLE
                + " WHERE " + ATTENDSTABLE + "." + classAssignedID.getFieldName() + " = " + classAssignedID.getDBFormattedString(teachesTable)
                + " AND " + ATTENDSTABLE + "." + USRASSIGNEDID + " = " + USERTABLE + "." + USRASSIGNEDID
                + " ORDER BY " + USRLNAME + ", " + USRFNAME
                + ";";

        ArrayList al =
            classTable.performDatabaseQuery(qs, new Item[] { assignedID, fname, lname});

        returnItems = new Item[al.size()][3];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] =
                new StringItem(assignedID.getFieldName(), (String) result.get(assignedID.getFieldName()), false);
            returnItems[i][1] =
                new StringItem(fname.getFieldName(), (String) result.get(fname.getFieldName()), false);
            returnItems[i][2] =
                new StringItem(lname.getFieldName(), (String) result.get(lname.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads summary information of the classes that implement a course.
     * @param courseAssignedID The auto-generated course ID of interest.
     * @return A list of class auto-generated IDs, school-assigned IDs, and times.
     */
    public Item[][] loadCourseClassList(Item courseAssignedID) {
        Item returnItems[][];
        StringItem classAssignedID = new StringItem(CLASSASSIGNEDID, "", false);
        StringItem schoolID = new StringItem(CLASSSCHOOLID, "", false);
        StringItem time = new StringItem(CLASSTIME, "", false);

        String qs =
            "SELECT " + classAssignedID.getFieldName() + ", " + schoolID.getFieldName() + ", " + time.getFieldName()
                + " FROM " + CLASSTABLE
                + " WHERE " + COURSEASSIGNEDID + " = " + courseAssignedID.getDBFormattedString(classTable)
                + ";";

        ArrayList al =
            classTable.performDatabaseQuery(qs, new Item[] { classAssignedID, schoolID, time });

        returnItems = new Item[al.size()][3];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] =
                new StringItem(classAssignedID.getFieldName(), (String) result.get(classAssignedID.getFieldName()), false);
            returnItems[i][1] =
                new StringItem(schoolID.getFieldName(), (String) result.get(schoolID.getFieldName()),false);
            returnItems[i][2] =
                new StringItem(time.getFieldName(), (String) result.get(time.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads summary information of parents.
     * @return A list of all parent auto-generated IDs, school-assigned IDs, first names, and last names.
     */
    public Item[][] loadEditorParentList() {
        Item returnItems[][];
        StringItem assignedid = new StringItem(USRASSIGNEDID, "", false);
        StringItem schoolid = new StringItem(USRSCHOOLID, "", false);
        StringItem firstname = new StringItem(USRFNAME, "", false);
        StringItem lastname = new StringItem(USRLNAME, "", false);
        String queryString = "SELECT "+ assignedid.getFieldName() +", " + schoolid.getFieldName() +
                                        ", " + firstname.getFieldName() + ", " + lastname.getFieldName() +
                                        "  FROM " + USERTABLE + " WHERE " + USRTYPE + " = '" + PARENT + "' " +
                                        " ORDER BY " + lastname.getFieldName() + ";";
        ArrayList al = userTable.performDatabaseQuery(queryString, new Item[] {assignedid, schoolid, firstname, lastname});

        returnItems = new Item[al.size()][4];
        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          returnItems[i][0] = new StringItem(assignedid.getFieldName(), (String) result.get(assignedid.getFieldName()), false);
          returnItems[i][1] = new StringItem(schoolid.getFieldName(), (String) result.get(schoolid.getFieldName()), false);
          returnItems[i][2] = new StringItem(firstname.getFieldName(), (String) result.get(firstname.getFieldName()), false);
          returnItems[i][3] = new StringItem(lastname.getFieldName(), (String) result.get(lastname.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads summary information of all courses.
     * @return A list of all course auto-generated IDs, school-assigned IDs, and short names.
     */
    public Item[][] loadEditorCourseList() {
        Item returnItems[][];
        StringItem assignedid =
            new StringItem(SchoolDBSQL.COURSEASSIGNEDID, "", false);
        StringItem schoolid = new StringItem(COURSESCHOOLID, "", false);
        StringItem shortname = new StringItem(COURSESHORTNAME, "", false);
        String queryString =
            "SELECT " + assignedid.getFieldName() + ", " + schoolid.getFieldName() + ", " + shortname.getFieldName()
                + "  FROM " + COURSETABLE
                + " ORDER BY " + schoolid.getFieldName()
                + ";";
        ArrayList al =
            userTable.performDatabaseQuery(queryString, new Item[] { assignedid, schoolid, shortname });

        returnItems = new Item[al.size()][3];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] =
                new StringItem(assignedid.getFieldName(), (String) result.get(assignedid.getFieldName()), false);
            returnItems[i][1] =
                new StringItem(schoolid.getFieldName(), (String) result.get(schoolid.getFieldName()), false);
            returnItems[i][2] =
                new StringItem(shortname.getFieldName(), (String) result.get(shortname.getFieldName()), false);
        }

        return returnItems;
    }

    /**
     * Loads summary information of the classes a teacher teaches.
     * @param teacherAssignedID The auto-generated ID of the teacher of interest.
     * @return A list of class auto-generated IDs, school-assigned IDs, and times.
     */
    public Item[][] loadTeacherAssignedClassList(Item teacherAssignedID) {
        Item returnItems[][];
        StringItem assignedid = new StringItem(CLASSASSIGNEDID, "", false);
        StringItem schoolid = new StringItem(CLASSSCHOOLID, "", false);
        StringItem time = new StringItem(CLASSTIME, "", false);

        String qs =
            "SELECT " + TEACHESTABLE + "." + CLASSASSIGNEDID + ", " + schoolid.getFieldName() + ", " + time.getFieldName()
                + " FROM " + CLASSTABLE + ", " + TEACHESTABLE
                + " WHERE " + CLASSTABLE + "." + CLASSASSIGNEDID + " = " + TEACHESTABLE + "." + CLASSASSIGNEDID
                + " AND " + TEACHESTABLE + "." + teacherAssignedID.getFieldName() + " = " + teacherAssignedID.getDBFormattedString(classTable)
                + ";";

        ArrayList al =
            teachesTable.performDatabaseQuery(qs, new Item[] { assignedid, schoolid, time });

        returnItems = new Item[al.size()][3];
        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            returnItems[i][0] =
                new StringItem(assignedid.getFieldName(), (String) result.get(assignedid.getFieldName()), false);
            returnItems[i][1] =
                new StringItem(schoolid.getFieldName(), (String) result.get(schoolid.getFieldName()), false);
            returnItems[i][2] =
                new StringItem(time.getFieldName(), (String) result.get(time.getFieldName()), false);
        }

        return returnItems;
    }


    /**
     * Deletes a user.
     * @param id The auto-generated ID of the user to delete.
     */
    public void deleteUser(Item id) {
        String deleteQuery =
            "DELETE FROM "
                + USERTABLE
                + " WHERE " + USRASSIGNEDID + " = " + id.getDBFormattedString(userTable)
                + ";";
        userTable.performDatabaseUpdate(deleteQuery);
    }

    /**
     * Deletes a class.
     * @param id The auto-generated ID of the class to delete.
     */
    public void deleteClass(Item id) {
        String deleteQuery =
            "DELETE FROM " + CLASSTABLE
                + " WHERE " + CLASSASSIGNEDID + " = " + id.getDBFormattedString(classTable)
                + ";";
        classTable.performDatabaseUpdate(deleteQuery);
    }

    /**
     * Deletes a course.
     * @param id The auto-generated ID of the course to delete.
     */
    public void deleteCourse(Item id) {
        String deleteQuery =
            "DELETE FROM " + COURSETABLE
                + " WHERE " + COURSEASSIGNEDID + " = " + id.getDBFormattedString(courseTable)
                + ";";
        courseTable.performDatabaseUpdate(deleteQuery);
    }

    /**
     * Deletes a parent's relationships.
     * @param id The auto-generated ID of the parent whose relationships are to be deleted.
     */
    public void deleteParentRelation(Item id) {
        String deleteQuery =
            "DELETE FROM " + RELATESTOTABLE
                + " WHERE " + PARENTUSRASSIGNEDID + " = " + id.getDBFormattedString(relatesToTable)
                + ";";
        relatesToTable.performDatabaseUpdate(deleteQuery);
    }

    /**
     * Deletes a student's relationships.
     * @param id The auto-generated ID of the student whose relationships are to be deleted.
     * @param id
     */
    public void deleteStudentRelation(Item id) {
        String deleteQuery =
            "DELETE FROM " + RELATESTOTABLE
                + " WHERE " + STUDENTUSRASSIGNEDID + " = " + id.getDBFormattedString(relatesToTable)
                + ";";
        relatesToTable.performDatabaseUpdate(deleteQuery);
    }

    /**
     * Deletes all associations relating to either a homework id or an attachment id.
     * @param id The ID of the homework whose attachment associations should be deleted.
     */
    public void deleteHWIncludes(Item id) {
        String deleteQuery =
            "DELETE FROM " + HWINCLUDESTABLE
                + " WHERE " + id.getFieldName() + " = " + id.getDBFormattedString(hwIncludesTable)
                + ";";
        hwIncludesTable.performDatabaseUpdate(deleteQuery);
    }

    /**
     * Deletes all associations between an attachment and a homework.
     * @param id
     */
    public void deleteHWAttachment(Item id) {
        String deleteQuery =
            "DELETE FROM " + ATTACHMENTTABLE
                + " WHERE " + id.getFieldName() + " = " + id.getDBFormattedString(attachmentTable)
                + ";";
        attachmentTable.performDatabaseUpdate(deleteQuery);
        deleteHWIncludes(id);
    }

    /**
     * Deletes all associations between a message and its attachments.
     * Warning: This can leave dangling attachments. This needs to be fixed.
     * @param id The ID of the message whose attachment associations should be deleted.
     */
    public void deleteMsgIncludes(Item id) {
        String deleteQuery =
            "DELETE FROM " + MSGINCLUDESTABLE
                + " WHERE " + MSGASSIGNEDID + " = " + id.getDBFormattedString(msgIncludesTable)
                + ";";
        msgIncludesTable.performDatabaseUpdate(deleteQuery);
    }

    /**
     * Deletes all associations between a message and its attachments.
     * Warning: This can leave dangling attachments. This needs to be fixed.
     * @param id The ID of the message whose attachment associations should be deleted.
     */
    public void deleteAttendance(Item sID, Item cID, Item date) {
        String deleteQuery =
            "DELETE FROM " + ATTENDANCETABLE
                + " WHERE " + sID.getFieldName() + " = " + sID.getDBFormattedString(msgIncludesTable)
                + " AND " + cID.getFieldName() + " = " + cID.getDBFormattedString(msgIncludesTable)
                + " AND " + date.getFieldName() + " = " + date.getDBFormattedString(msgIncludesTable)
                + ";";
        attendanceTable.performDatabaseUpdate(deleteQuery);
    }

    /**
     * Saves the relationship between a teacher and a list of classes.
     * @param teacherid The auto-generated ID of the teacher.
     * @param classes A list of auto-generated IDs of classes.
     */
    public void saveTeacherClass(Item teacherid, Item classes[]) {
        String saveQuery = null;
        String deleteQuery =
            "DELETE FROM " + TEACHESTABLE
                + " WHERE " + teacherid.getFieldName() + " = " + teacherid.getDBFormattedString(classTable)
                + ";";
        classTable.performDatabaseUpdate(deleteQuery);
        for (int i = 0; i < classes.length; i++) {
            saveQuery =
                "INSERT INTO " + TEACHESTABLE
                    + " (" + teacherid.getFieldName() + ", " + classes[i].getFieldName() + ")"
                    + " VALUES (" + teacherid.getDBFormattedString(classTable) + "," + classes[i].getDBFormattedString(classTable)
                    + ");";
            classTable.performDatabaseUpdate(saveQuery);
        }
    }

    /**
     * Saves the relationship between a student and a list of classes.
     * @param studentid The auto-generated ID of the student.
     * @param classes A list of auto-generated IDs of classes.
     */
    public void saveStudentClass(Item studentid, Item classes[]) {
        String saveQuery = null;
        String deleteQuery =
            "DELETE FROM " + ATTENDSTABLE
                + " WHERE " + studentid.getFieldName() + " = " + studentid.getDBFormattedString(classTable)
                + ";";
        classTable.performDatabaseUpdate(deleteQuery);
        for (int i = 0; i < classes.length; i++) {
            saveQuery =
                "INSERT INTO " + ATTENDSTABLE
                    + " (" + studentid.getFieldName() + ", " + classes[i].getFieldName() + ")"
                    + " VALUES (" + studentid.getDBFormattedString(classTable) + "," + classes[i].getDBFormattedString(classTable)
                    + ");";
            classTable.performDatabaseUpdate(saveQuery);
        }
    }

    // The following section contains methods that move records from normal tables to tables in a
    // deleted state. The following dependencies exist:
    //  When a user is deleted - all dependent to attends, teaches, announces (not yet implemented),
    //          grade, and contacts should be 'deleted'
    //  When a relation is deleted, nothing else needs to be deleted
    //  When a message is deleted, all dependent contacts and msgincludes should be deleted
    //  When a course is deleted, all dependent classes should be deleted
    //  When a class is deleted, all dependent attends, teaches, homework, contacts, and announces
    //          should be deleted
    //  When homework is deleted, all dependent grades and hwincludes should be deleted
    //  When a grade is deleted, nothing else needs to be deleted
    //  When a teaches is deleted, nothing else needs to be deleted
    //  When an attends is deleted, nothing else needs to be deleted
    //  When an announces is deleted, a message should be deleted only if there are no other
    //          references to that message in the announces table (TODO NOT YET IMPLEMENTED)
    //  When a contacts is deleted, a message should be deleted only if htere are no other
    //          references to that message in the contacts table
    //  When an attachment is deleted, all dependent msgIncludes and hwincludes should be deleted
    //  When a msgIncludes is deleted, the attachment should be deleted only if there are no other
    //          references to that attachment
    //  When a hwIncludes is deleted, the attachment should be deleted only if there are no other
    //          references to that attachment

    // use when deleting attachment
    /**
     * Moves all homework associated with an attachment ID and associated attachment relationships
     * to the deleted table.
     * @param attachmentID The auto-generated ID of the attachment of interest.
     */
    public void moveHWIncludeToDeletedByAttachment(Item attachmentID) {
        Item matchingItem = new IntegerItem(HWASSIGNEDID, "", false);

        String qs = "SELECT " + matchingItem.getFieldName()
                        + " FROM " + HWINCLUDESTABLE
                        + " WHERE " + attachmentID.getFieldName() + " = " + attachmentID.getDBFormattedString(hwIncludesTable) + ";";

        ArrayList al = hwIncludesTable.performDatabaseQuery(qs, new Item[] {matchingItem});

        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          matchingItem.setValue((String) result.get(matchingItem.getFieldName()));
          delHWIncludesTable.save(new Item[] {attachmentID, matchingItem}, new Item[] {attachmentID, matchingItem}, DBSQLAccessor.AND);
          hwIncludesTable.delete(new Item[] {attachmentID, matchingItem}, DBSQLAccessor.AND);
        }
    }

    // use when deleting attachment
    /**
     * Moves all messages associated with an attachment ID and associated attachment relationships
     * to the deleted table.
     * @param attachmentID The auto-generated ID of the attachment of interest.
     */
    public void moveMsgIncludeToDeletedByAttachment(Item attachmentID) {
        Item matchingItem = new IntegerItem(MSGASSIGNEDID, "", false);

        String qs = "SELECT " + matchingItem.getFieldName()
                        + " FROM " + MSGINCLUDESTABLE
                        + " WHERE " + attachmentID.getFieldName() + " = " + attachmentID.getDBFormattedString(msgIncludesTable) + ";";

        ArrayList al = msgIncludesTable.performDatabaseQuery(qs, new Item[] {matchingItem});

        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          matchingItem.setValue((String) result.get(matchingItem.getFieldName()));
          delMsgIncludesTable.save(new Item[] {attachmentID, matchingItem}, new Item[] {attachmentID, matchingItem}, DBSQLAccessor.AND);
          msgIncludesTable.delete(new Item[] {attachmentID, matchingItem}, DBSQLAccessor.AND);
        }
    }

    /**
     * Moves the homework associated with a homework ID and associated attachment relationships
     * to the deleted table and cleans up attachments.
     * @param hwID The auto-generated ID of the homework of interest.
     */
    public void moveHWIncludeToDeletedByHomework(Item hwID) {
        Item matchingItem = new IntegerItem(ATTASSIGNEDID, "", false);

        String qs = "SELECT " + matchingItem.getFieldName()
                        + " FROM " + HWINCLUDESTABLE
                        + " WHERE " + hwID.getFieldName() + " = " + hwID.getDBFormattedString(hwIncludesTable) + ";";

        ArrayList al = hwIncludesTable.performDatabaseQuery(qs, new Item[] {matchingItem});

        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          matchingItem.setValue((String) result.get(matchingItem.getFieldName()));
          delHWIncludesTable.save(new Item[] {hwID, matchingItem}, new Item[] {hwID, matchingItem}, DBSQLAccessor.AND);
          hwIncludesTable.delete(new Item[] {hwID, matchingItem}, DBSQLAccessor.AND);
          cleanUpAttachment(AttachmentContainer.getAttachmentContainer(matchingItem.toString(), unprefixedDataSource));
        }
    }

    /**
     * Moves the message associated with a message ID to the deleted table and
     * cleans up related attachments.
     * @param msgID The auto-generated ID of the message of interest.
     */
    public void moveMsgIncludeToDeletedByMessage(Item msgID) {
        Item matchingItem = new IntegerItem(ATTASSIGNEDID, "", false);

        String qs = "SELECT " + matchingItem.getFieldName()
                        + " FROM " + MSGINCLUDESTABLE
                        + " WHERE " + msgID.getFieldName() + " = " + msgID.getDBFormattedString(msgIncludesTable) + ";";

        ArrayList al = msgIncludesTable.performDatabaseQuery(qs, new Item[] {matchingItem});

        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          matchingItem.setValue((String) result.get(matchingItem.getFieldName()));
          delMsgIncludesTable.save(new Item[] {msgID, matchingItem}, new Item[] {msgID, matchingItem}, DBSQLAccessor.AND);
          msgIncludesTable.delete(new Item[] {msgID, matchingItem}, DBSQLAccessor.AND);
          cleanUpAttachment(AttachmentContainer.getAttachmentContainer(matchingItem.toString(), unprefixedDataSource));
        }
    }

    /**
     * Moves an attachment and its relationships to the deleted tables.
     * @param at The attachment to move.
     */
    public void moveAttachmentToDeleted(AttachmentContainer at) {
        moveHWIncludeToDeletedByAttachment(at.getAssignedAttachmentID());
        moveMsgIncludeToDeletedByAttachment(at.getAssignedAttachmentID());
        delAttachmentTable.save(at.getValidationItems(), new Item[] {at.getAssignedAttachmentID()}, DBSQLAccessor.AND);
        attachmentTable.delete(new Item[] {at.getAssignedAttachmentID()}, DBSQLAccessor.AND);
    }

    /**
     * Ensures that any attachments without relationships to other tables are moved to the deleted table.
     * @param ac The attachment to move.
     */
    public void cleanUpAttachment(AttachmentContainer ac) {
        String qs = "SELECT * FROM " + HWINCLUDESTABLE + " WHERE " + ac.getAssignedAttachmentID().getFieldName() + " = " + ac.getAssignedAttachmentID().getDBFormattedString(hwIncludesTable) + ";";
        if (hwIncludesTable.performDatabaseQuery(qs, new Item[] {ac.getAssignedAttachmentID()}).size() == 0) {
            qs = "SELECT * FROM " + MSGINCLUDESTABLE + " WHERE " + ac.getAssignedAttachmentID().getFieldName() + " = " + ac.getAssignedAttachmentID().getDBFormattedString(msgIncludesTable) + ";";
            if (msgIncludesTable.performDatabaseQuery(qs, new Item[] {ac.getAssignedAttachmentID()}).size() == 0) {
                moveAttachmentToDeleted(ac);
            }
        }
    }

    /**
     * Ensures that any messages without relationships to other tables are moved to the deleted table.
     * @param messageID The message to move.
     */
    public void cleanUpMessage(Item messageID) {
        String qs = "SELECT * FROM " + CONTACTSTABLE + " WHERE " + messageID.getFieldName() + " = " + messageID.getDBFormattedString(hwIncludesTable) + ";";
        if (hwIncludesTable.performDatabaseQuery(qs, new Item[] {messageID}).size() == 0) {
            moveMsgIncludeToDeletedByMessage(messageID);
            this.moveMessageToDeleted(MessageContainer.getMessageContainer(messageID.toString(), unprefixedDataSource));
        }
    }

    /**
     * Moves a contact to the deleted table and cleans up messages.
     * @param cc The contact to move.
     */
    public void moveContactToDeleted(ContactContainer cc) {
        delContactsTable.save(cc.getValidationItems(), new Item[] {cc.getMessageAssignedID(), cc.getToID(), cc.getFromID()}, DBSQLAccessor.AND);
        contactsTable.delete(new Item[] {cc.getMessageAssignedID(), cc.getToID(), cc.getFromID()}, DBSQLAccessor.AND);
        cleanUpMessage(cc.getMessageAssignedID());
    }

    /**
     * Moves all contacts associated with a class to the deleted table.
     * @param cc The class of interest.
     */
    public void moveContactToDeletedByClass(ClassContainer cc) {
        Item toID = new IntegerItem(CTTOID, "", false);
        Item fromID = new IntegerItem(CTFROMID, "", false);
        Item msgID = new IntegerItem(MSGASSIGNEDID, "", false);

        String qs = "SELECT " + toID.getFieldName() + ", " + fromID.getFieldName() + ", "+ msgID.getFieldName()
                        + " FROM " + CONTACTSTABLE
                        + " WHERE " + cc.getClassAssignedID().getFieldName() + " = " + cc.getClassAssignedID().getDBFormattedString(contactsTable) + ";";

        ArrayList al = contactsTable.performDatabaseQuery(qs, new Item[] {toID, fromID, msgID});

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            toID.setValue((String) result.get(toID.getFieldName()));
            fromID.setValue((String) result.get(fromID.getFieldName()));
            msgID.setValue((String) result.get(msgID.getFieldName()));
//            moveContactToDeleted(new ContactContainer(msgID.toString(), toID.toString(), fromID.toString()));
            moveContactToDeleted(ContactContainer.getContactContainer(msgID.toString(), toID.toString(), fromID.toString(), unprefixedDataSource));
        }
    }

    /**
     * Moves all contacts associated with a user to the deleted table.
     * @param userID The auto-generated ID of the user of interest.
     */
    public void moveContactToDeletedByUser(String userID) {
        Item toID = new IntegerItem(CTTOID, userID, false);
        Item fromID = new IntegerItem(CTFROMID, "", false);
        Item msgID = new IntegerItem(MSGASSIGNEDID, "", false);

        String qs = "SELECT " + fromID.getFieldName() + ", "+ msgID.getFieldName()
                        + " FROM " + CONTACTSTABLE
                        + " WHERE " + toID.getFieldName() + " = " + toID.getDBFormattedString(contactsTable) + ";";

        ArrayList al = contactsTable.performDatabaseQuery(qs, new Item[] {fromID, msgID});

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            fromID.setValue((String) result.get(fromID.getFieldName()));
            msgID.setValue((String) result.get(msgID.getFieldName()));
//          moveContactToDeleted(new ContactContainer(msgID.toString(), toID.toString(), fromID.toString()));
            moveContactToDeleted(ContactContainer.getContactContainer(msgID.toString(), toID.toString(), fromID.toString(), unprefixedDataSource));
// TODO fix the next line
//          removeContactFromBucket(msgID.toString(), toID.toString(), fromID.toString());
        }
    }

    /**
     * Moves an announcement to the deleted table and cleans up the associated message.
     * @param ac The announcement to move.
     */
    public void moveAnnounceToDeleted(AnnouncementContainer ac) {
        delAnnouncesTable.save(ac.getValidationItems(), new Item[] {ac.getAssignedMessageID(), ac.getClassID(), ac.getFromID()}, DBSQLAccessor.AND);
        announcesTable.delete(new Item[] {ac.getAssignedMessageID(), ac.getClassID(), ac.getFromID()}, DBSQLAccessor.AND);
        cleanUpMessage(ac.getAssignedMessageID());
    }

    /**
     * Not yet implemented.
     * Moves all announcements related to a class to the deleted table.
     * @param cc The class of interest.
     */
    public void moveAnnounceToDeletedByClass(ClassContainer cc) {
//      Item fromID = new IntegerItem(CTTOID, "", false);
//      Item msgID = new IntegerItem(MSGASSIGNEDID, "", false);
//
//      String qs = "SELECT " + cc.getClassSchoolID().getFieldName() + ", " + fromID.getFieldName() + ", "+ msgID.getFieldName()
//                      + " FROM " + announcesTable
//                      + " WHERE " + cc.getClassAssignedID().getFieldName() + " = " + cc.getClassAssignedID().getDBFormattedString(homeworkTable) + ";";
//
//      ArrayList al = homeworkTable.performDatabaseQuery(qs, new Item[] {toID, fromID, msgID});
//
//      for (int i = 0; i < al.size(); i++) {
//          Hashtable result = (Hashtable) al.get(i);
//          fromID.setValue((String) result.get(fromID.getFieldName()));
//          msgID.setValue((String) result.get(msgID.getFieldName()));
//          moveContactToDeleted(new AnnouncementContainer(msgID.toString(), toID.toString(), fromID.toString()));
//      }
    }

    /**
     * Not yet implemented.
     * Moves all announcements related to a user to the deleted table.
     * @param userID The user of interest.
     */
    public void moveAnnounceToDeletedByUser(Item userID) {

    }

    /**
     * Moves an attendance relationship to the deleted table.
     * @param classID The auto-generated class ID of interest.
     * @param userID The auto-generated user ID of interest.
     */
    public void moveAttendToDeleted(Item classID, Item userID) {
        delAttendsTable.save(new Item[] {classID, userID}, new Item[] {classID, userID}, DBSQLAccessor.AND);
        attendsTable.delete(new Item[] {classID, userID}, DBSQLAccessor.AND);
    }

    /**
     * Moves all attendance relationships for a specific class to the deleted table.
     * @param classID The auto-generated ID of the class of interest.
     */
    public void moveAttendToDeletedByClass(Item classID) {
        Item studentID = new IntegerItem(USRASSIGNEDID, "", false);

        String qs = "SELECT " + studentID.getFieldName()
                        + " FROM " + ATTENDSTABLE
                        + " WHERE " + classID.getFieldName() + " = " + classID.getDBFormattedString(attendsTable) + ";";

        ArrayList al = attendsTable.performDatabaseQuery(qs, new Item[] {studentID});

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            studentID.setValue((String) result.get(studentID.getFieldName()));
            moveAttendToDeleted(classID, studentID);
        }
    }

    /**
     * Moves all attendance relationships for a specific user to the deleted table.
     * @param studentID The auto-generated ID of the user of interest.
     */
    public void moveAttendToDeletedByUser(Item studentID) {
        Item classID = new IntegerItem(CLASSASSIGNEDID, "", false);

        String qs = "SELECT " + classID.getFieldName()
                        + " FROM " + ATTENDSTABLE
                        + " WHERE " + studentID.getFieldName() + " = " + studentID.getDBFormattedString(attendsTable) + ";";

        ArrayList al = attendsTable.performDatabaseQuery(qs, new Item[] {classID});

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            classID.setValue((String) result.get(classID.getFieldName()));
            moveAttendToDeleted(classID, studentID);
        }
    }

    /**
     * Moves a teaches relationship to the deleted table.
     * @param classID The auto-generated class ID of interest.
     * @param userID The auto-generated user ID of interest.
     */
    public void moveTeachToDeleted(Item classID, Item userID) {
        delTeachesTable.save(new Item[] {classID, userID}, new Item[] {classID, userID}, DBSQLAccessor.AND);
        teachesTable.delete(new Item[] {classID, userID}, DBSQLAccessor.AND);
    }

    /**
     * Moves all teaches relationships for a specific class to the deleted table.
     * @param classID The auto-generated ID of the class of interest.
     */
    public void moveTeachToDeletedByClass(Item classID) {
        Item studentID = new IntegerItem(USRASSIGNEDID, "", false);

        String qs = "SELECT " + studentID.getFieldName()
                        + " FROM " + TEACHESTABLE
                        + " WHERE " + classID.getFieldName() + " = " + classID.getDBFormattedString(teachesTable) + ";";

        ArrayList al = teachesTable.performDatabaseQuery(qs, new Item[] {studentID});

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            studentID.setValue((String) result.get(studentID.getFieldName()));
            moveTeachToDeleted(classID, studentID);
        }
    }

    /**
     * Moves all attendance relationships for a specific teacher to the deleted table.
     * @param teacherID The auto-generated ID of the teacher of interest.
     */
    public void moveTeachToDeletedByUser(Item teacherID) {
        Item classID = new IntegerItem(CLASSASSIGNEDID, "", false);

        String qs = "SELECT " + classID.getFieldName()
                        + " FROM " + TEACHESTABLE
                        + " WHERE " + teacherID.getFieldName() + " = " + teacherID.getDBFormattedString(teachesTable) + ";";

        ArrayList al = teachesTable.performDatabaseQuery(qs, new Item[] {classID});

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            classID.setValue((String) result.get(classID.getFieldName()));
            moveTeachToDeleted(classID, teacherID);
        }
    }

    /**
     * Moves a parent-child relationship to the deleted table.
     * @param classID The auto-generated class ID of interest.
     * @param parentID The auto-generated parent ID of interest.
     * @param studentID The auto-generated student ID of interest.
     */
    public void moveRelationToDeleted(Item parentID, Item studentID) {
        delRelatesToTable.save(new Item[] {parentID, studentID}, new Item[] {parentID, studentID}, DBSQLAccessor.AND);
        relatesToTable.delete(new Item[] {parentID, studentID}, DBSQLAccessor.AND);
    }

    /**
     * Moves all parent-child relationships for a parent to the deleted table.
     * @param parentIDString The auto-generated parent ID of interest.
     */
    public void moveRelationToDeletedByParent(String parentIDString) {
        Item parentID = new IntegerItem(PARENTUSRASSIGNEDID, parentIDString, false);
        Item studentID = new IntegerItem(STUDENTUSRASSIGNEDID, "", false);

        String qs = "SELECT " + studentID.getFieldName()
                        + " FROM " + RELATESTOTABLE
                        + " WHERE " + parentID.getFieldName() + " = " + parentID.getDBFormattedString(relatesToTable) + ";";

        ArrayList al = relatesToTable.performDatabaseQuery(qs, new Item[] {studentID});

        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          studentID.setValue((String) result.get(studentID.getFieldName()));
          moveRelationToDeleted(parentID, studentID);
        }
    }

    /**
     * Moves all parent-child relationships for a student to the deleted table.
     * @param studentIDString The auto-generated student ID of interest.
     */
    public void moveRelationToDeletedByStudent(String studentIDString) {
        Item parentID = new IntegerItem(PARENTUSRASSIGNEDID, "", false);
        Item studentID = new IntegerItem(STUDENTUSRASSIGNEDID, studentIDString, false);

        String qs = "SELECT " + parentID.getFieldName()
                        + " FROM " + RELATESTOTABLE
                        + " WHERE " + studentID.getFieldName() + " = " + studentID.getDBFormattedString(relatesToTable) + ";";

        ArrayList al = relatesToTable.performDatabaseQuery(qs, new Item[] {parentID});

        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          parentID.setValue((String) result.get(parentID.getFieldName()));
          moveRelationToDeleted(parentID, studentID);
        }
    }

    /**
     * Moves a grade to the deleted table.
     * @param gc The grade to be moved.
     */
    public void moveGradeToDeleted(GradeContainer gc) {
        delGradeTable.save(gc.getValidationItems(), new Item[] {gc.getClassID(), gc.getStudentID(), gc.getHomeworkID()}, DBSQLAccessor.AND);
        gradeTable.delete(new Item[] {gc.getClassID(), gc.getStudentID(), gc.getHomeworkID()}, DBSQLAccessor.AND);
    }

    /**
     * Moves all grades associated with a student to the deleted table.
     * @param studentID The auto-generated student ID of interest.
     */
    public void moveGradeToDeletedByStudent(Item studentID) {
        Item homeworkID = new IntegerItem(HWASSIGNEDID, "", false);
        Item classID = new IntegerItem(CLASSASSIGNEDID, "", false);
        Item grade = new StringItem(GRADESCORE, "", false);

        String qs = "SELECT " + homeworkID.getFieldName() + ", " + classID.getFieldName() + ", " + grade.getFieldName()
                        + " FROM " + GRADETABLE
                        + " WHERE " + studentID.getFieldName() + " = " + studentID.getDBFormattedString(gradeTable) + ";";

        ArrayList al = relatesToTable.performDatabaseQuery(qs, new Item[] {homeworkID, classID, grade});

        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          homeworkID.setValue((String) result.get(homeworkID.getFieldName()));
          classID.setValue((String) result.get(classID.getFieldName()));
          grade.setValue((String) result.get(grade.getFieldName()));
          delGradeTable.save(new Item[] {classID, studentID, homeworkID, grade}, new Item[] {classID, studentID, homeworkID}, DBSQLAccessor.AND);
          gradeTable.delete(new Item[] {classID, studentID, homeworkID}, DBSQLAccessor.AND);
        }
    }

    /**
     * Moves all grades associated with a homework to the deleted table.
     * @param hc The homework of interest.
     */
    public void moveGradeToDeletedByHomework(HomeworkContainer hc) {
        Item studentID = new IntegerItem(USRASSIGNEDID, "", false);
        Item classID = new IntegerItem(CLASSASSIGNEDID, "", false);
        Item grade = new StringItem(GRADESCORE, "", false);

        String qs = "SELECT " + studentID.getFieldName() + ", " + classID.getFieldName() + ", " + grade.getFieldName()
                        + " FROM " + GRADETABLE
                        + " WHERE " + hc.getHomeworkID().getFieldName() + " = " + hc.getHomeworkID().getDBFormattedString(gradeTable) + ";";

        ArrayList al = relatesToTable.performDatabaseQuery(qs, new Item[] {studentID, classID, grade});

        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          studentID.setValue((String) result.get(studentID.getFieldName()));
          classID.setValue((String) result.get(classID.getFieldName()));
          grade.setValue((String) result.get(grade.getFieldName()));
          delGradeTable.save(new Item[] {classID, hc.getHomeworkID(), studentID, grade}, new Item[] {classID, hc.getHomeworkID(), studentID}, DBSQLAccessor.AND);
          gradeTable.delete(new Item[] {classID, hc.getHomeworkID(), studentID}, DBSQLAccessor.AND);
        }
    }

    /**
     * Moves all grades associated with a class to the deleted table.
     * @param cc The class of interest.
     */
    public void moveGradeToDeletedByClass(ClassContainer cc) {
        Item studentID = new IntegerItem(USRASSIGNEDID, "", false);
        Item homeworkID = new IntegerItem(HWASSIGNEDID, "", false);
        Item grade = new StringItem(GRADESCORE, "", false);

        String qs = "SELECT " + studentID.getFieldName() + ", " + homeworkID.getFieldName() + ", " + grade.getFieldName()
                        + " FROM " + GRADETABLE
                        + " WHERE " + cc.getClassAssignedID().getFieldName() + " = " + cc.getClassAssignedID().getDBFormattedString(gradeTable) + ";";

        ArrayList al = relatesToTable.performDatabaseQuery(qs, new Item[] {studentID, homeworkID, grade});

        for (int i = 0; i < al.size(); i++) {
          Hashtable result = (Hashtable) al.get(i);
          studentID.setValue((String) result.get(studentID.getFieldName()));
          homeworkID.setValue((String) result.get(homeworkID.getFieldName()));
          grade.setValue((String) result.get(grade.getFieldName()));
          delGradeTable.save(new Item[] {homeworkID, cc.getClassAssignedID(), studentID, grade}, new Item[] {homeworkID, cc.getClassAssignedID(), studentID}, DBSQLAccessor.AND);
          gradeTable.delete(new Item[] {homeworkID, cc.getClassAssignedID(), studentID}, DBSQLAccessor.AND);
        }
    }

    /**
     * Moves a homework to the deleted table and cleans up the related grades.
     * @param hw The homework to move.
     */
    public void moveHomeworkToDeleted(HomeworkContainer hw) {
        delHomeworkTable.save(hw.getValidationItems(), new Item[] {hw.getHomeworkID()}, DBSQLAccessor.AND);
        gradeTable.delete(new Item[] {hw.getHomeworkID()}, DBSQLAccessor.AND);

        moveGradeToDeletedByHomework(hw);
        moveHWIncludeToDeletedByHomework(hw.getHomeworkID());
        delHomeworkTable.save(hw.getValidationItems(), new Item[] {hw.getHomeworkID()}, DBSQLAccessor.AND);
        homeworkTable.delete(new Item[] {hw.getHomeworkID()}, DBSQLAccessor.AND);
    }

    /**
     * Moves all homeworks associated with a class to the deleted table and cleans up the
     * related grades.
     * @param cc The class of interest.
     */
    public void moveHomeworkToDeletedByClass(ClassContainer cc) {
        Item matchingItem = new IntegerItem(HWASSIGNEDID, "", false);

        String qs = "SELECT " + matchingItem.getFieldName()
                        + " FROM " + HOMEWORKTABLE
                        + " WHERE " + cc.getClassAssignedID().getFieldName() + " = " + cc.getClassAssignedID().getDBFormattedString(homeworkTable) + ";";

        ArrayList al = homeworkTable.performDatabaseQuery(qs, new Item[] {matchingItem});

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            matchingItem.setValue((String) result.get(matchingItem.getFieldName()));
//            moveHomeworkToDeleted(new HomeworkContainer(matchingItem.toString(), cc.getClassAssignedID().toString()));
            moveHomeworkToDeleted(HomeworkContainer.getHomeworkContainer(matchingItem.toString(), unprefixedDataSource));
        }
    }

    /**
     * Moves a class to the deleted table and cleans up all associated homework, contacts, announcements,
     * attends relationships, and teaches relationships.
     * @param cc The class to move.
     */
    public void moveClassToDeleted(ClassContainer cc) {
        moveAttendToDeletedByClass(cc.getClassAssignedID());
        moveTeachToDeletedByClass(cc.getClassAssignedID());
        moveHomeworkToDeletedByClass(cc);
        moveContactToDeletedByClass(cc);
        moveAnnounceToDeletedByClass(cc);
        delClassTable.save(cc.getValidationItems(), new Item[] {cc.getClassAssignedID()}, DBSQLAccessor.AND);
        classTable.delete(new Item[] {cc.getClassAssignedID()}, DBSQLAccessor.AND);

    }

    /**
     * Moves a term to the deleted table
     * @param tc The class to move.
     */
    public void moveTermToDeleted(TermContainer tc) {
        delTermTable.save(tc.getValidationItems(), tc.getValidationItems(), DBSQLAccessor.AND);
        termTable.delete(tc.getValidationItems(), DBSQLAccessor.AND);

    }

    /**
     * Moves all classes to the deleted table (and does clean-up for all associations contained therein)
     * associated with a course.
     * @param courseID The auto-generated course ID of interest.
     */
    public void moveClassToDeletedByCourse(Item courseID) {
        Item matchingItem = new IntegerItem(CLASSASSIGNEDID, "", false);

        String qs = "SELECT " + matchingItem.getFieldName()
                        + " FROM " + CLASSTABLE
                        + " WHERE " + courseID.getFieldName() + " = " + courseID.getDBFormattedString(classTable) + ";";

        ArrayList al = classTable.performDatabaseQuery(qs, new Item[] {matchingItem});

        for (int i = 0; i < al.size(); i++) {
            Hashtable result = (Hashtable) al.get(i);
            matchingItem.setValue((String) result.get(matchingItem.getFieldName()));
//            moveClassToDeleted(new ClassContainer(matchingItem.toString()));
            moveClassToDeleted(ClassContainer.getClassContainer(matchingItem.toString(), unprefixedDataSource));
// TODO fix the next line
//          removeClassFromBucket(matchingItem.toString());
        }
    }

    /**
     * Moves a course and any associated classes to the deleted table.
     * @param cc The course of interest.
     */
    public void moveCourseToDeleted(CourseContainer cc) {
        moveClassToDeletedByCourse(cc.getCourseAssignedID());

        delCourseTable.save(cc.getValidationItems(), new Item[] {cc.getCourseAssignedID()}, DBSQLAccessor.AND);
        courseTable.delete(new Item[] {cc.getCourseAssignedID()}, DBSQLAccessor.AND);
    }

    /**
     * Moves a message and any associated attachments to the deleted table.
     * @param mc The message of interest.
     */
    public void moveMessageToDeleted(MessageContainer mc) {
        this.moveMsgIncludeToDeletedByMessage(mc.getAssignedMessageID());

        delMessageTable.save(mc.getValidationItems(), new Item[] {mc.getAssignedMessageID()}, DBSQLAccessor.AND);
        messageTable.delete(new Item[] {mc.getAssignedMessageID()}, DBSQLAccessor.AND);
    }

    /**
     * Moves a user, any associated grades, announcements, contacts, and any other associations to
     * deleted tables.
     * @param uc The user of interest.
     */
    public void moveUserToDeleted(UserContainer uc) {
        this.moveAttendToDeletedByUser(uc.getAssignedID());
        this.moveTeachToDeletedByUser(uc.getAssignedID());
        this.moveAnnounceToDeletedByUser(uc.getAssignedID());
        this.moveGradeToDeletedByStudent(uc.getAssignedID());
        this.moveContactToDeletedByUser(uc.getAssignedID().toString());

        delUserTable.save(uc.getValidationItems(), new Item[] {uc.getAssignedID()}, DBSQLAccessor.AND);
        userTable.delete(new Item[] {uc.getAssignedID()}, DBSQLAccessor.AND);
    }

    /**
     * Saves a user's password change.
     * @param userID The auto-generated ID of the user.
     * @param currentPassword The current password of the user.
     * @param newPassword The new password for the user.
     */
    public void changeUserPassword(Item assignedUserID, Item currentPassword, Item newPassword) {
        BooleanItem pswdChgReq = new BooleanItem(USRPSWDCHGREQ, "FALSE", true);
        userTable.save(new Item[] {newPassword, pswdChgReq}, new Item[] {assignedUserID, currentPassword}, DBSQLAccessor.AND);
    }

    /**
     * Indicates whether there is more than one user with the indicated school-assigned ID.
     * @param schoolAssignedID The school-assigned ID of the user.
     * @param userType The type of user
     * @return True if there is not more than one user of the same user type with the specified
     * school-assigned ID in the table.
     */
    public boolean isUniqueUserID(Item schoolAssignedID, Item userType) {
        boolean returnVal = false;
        String qs = "SELECT * "
                        + " FROM " + USERTABLE
                        + " WHERE " + schoolAssignedID.getFieldName() + " = " + schoolAssignedID.getDBFormattedString(userTable)
                        + " AND " + userType.getFieldName() + " = " + userType.getDBFormattedString(userTable)
                        + ";";

        if (userTable.performDatabaseQuery(qs, new Item[] {schoolAssignedID}).size() < 2) {
            returnVal = true;
        }
        return returnVal;
    }

    /**
     * Indicates whether there is more than one course with the indicated school-assigned ID.
     * @param schoolAssignedID The school-assigned ID of the course.
     * @return True if there is not more than one course with the specified
     * school-assigned ID in the table.
     */
    public boolean isUniqueCourseID(Item schoolAssignedID) {
        boolean returnVal = false;
        String qs = "SELECT * "
                        + " FROM " + COURSETABLE
                        + " WHERE " + schoolAssignedID.getFieldName() + " = " + schoolAssignedID.getDBFormattedString(courseTable)
                        + ";";

        if (courseTable.performDatabaseQuery(qs, new Item[] {schoolAssignedID}).size() < 2) {
            returnVal = true;
        }
        return returnVal;
    }

    /**
     * Indicates whether there is more than one class with the indicated school-assigned ID.
     * @param schoolAssignedID The school-assigned ID of the class.
     * @return True if there is not more than one class with the specified
     * school-assigned ID in the table.
     */
    public boolean isUniqueClassID(Item schoolAssignedID) {
        boolean returnVal = false;
        String qs = "SELECT * "
                        + " FROM " + CLASSTABLE
                        + " WHERE " + schoolAssignedID.getFieldName() + " = " + schoolAssignedID.getDBFormattedString(classTable)
                        + ";";

        if (classTable.performDatabaseQuery(qs, new Item[] {schoolAssignedID}).size() < 2) {
            returnVal = true;
        }
        return returnVal;
    }

}



